Insert NULL value from a table to another using Execute SQL Task on OLEDB connection
-
Thursday, April 26, 2012 2:35 PM
Hi everybody!
Can you help me to solve a problem that I am not able to find how I can handle it please ?
I am trying to do a simple thing : transfert data from a table to another and I am not able to handle insterting NULL values to the destination.
In order to explain de problem, I reproduced it into a sample project that is very basic. Here is the project structure :
Tables :
Source table
This is the source datatable
Destination table
This is the destination datatable
Control Flow
The control flows works this way :
- Get the source data from the source datatable on SQL Server
- Foreach record, insert a new record in the destination datatable
Data Flow Task
The Data Flow Task works this way :
- Get the source data from the source datatable on SQL Server
- Put the whole data in a resultset
- The resultset is stored in a package variable "DataStored" (Object)
OleDB datasource
The OleDB datasource retrives all the records from the source datatable
Resultset Destination
The destination resultset stores all the records from the source datatable
Foreach loop
The foreach loop iterates on each record of the resultset
aThere are 2 variable in the package to store the fields to copy :
- Field2 (int)
- Field2 (int)
The foreach loop is mapped on the "Field1" and "Field2" variable on the second and third index (the zero index is the primary key)
Execute SQL Task
The execute SQL task inserts each record to the destination datatable. The query needs the 2 parameters that I put into the variables (Field1 and Field2)
The variables are mapped to the "?" parameters.
And here is the problem! When Fields 2 from the source datatable is NULL, the package fails because of a conversion exception.
I understand that the variable type is a LONG one, and that is why it fails. But I don't know how to make it work...
I believe this is easy to do for SSIS experts, but I am a newbie...
Can you help me ?
Thank you.
Best regards.
Matteo, .NET Developer and System Engineer
All Replies
-
Thursday, April 26, 2012 2:40 PM
Why in the world are you doing single record inserts instead of just connecting your datasource to your destination?
In any event, all you need to do under your current setup is to change your variable types to DT_STR which does support a null value
Chuck
-
Thursday, April 26, 2012 2:42 PM
I would use an ISNULL(field2, -1) AS Field2 in your sql select statement, that way you always have data in your long variable, then wherever you had to chack for NULL, check for -1.
Jan D'Hondt - Database and .NET development
-
Thursday, April 26, 2012 3:59 PM
Why in the world are you doing single record inserts instead of just connecting your datasource to your destination?
In any event, all you need to do under your current setup is to change your variable types to DT_STR which does support a null value
Chuck
Hi Chuck.
This is just a sample. My real project is much more complex and I have a business logic that I cannot explain here...
Regards.
Matteo, .NET Developer and System Engineer
-
Thursday, April 26, 2012 4:30 PMto 'check' for , not 'chack'
Jan D'Hondt - Database and .NET development
-
Thursday, April 26, 2012 6:48 PM
I would use an ISNULL(field2, -1) AS Field2 in your sql select statement, that way you always have data in your long variable, then wherever you had to chack for NULL, check for -1.
Jan D'Hondt - Database and .NET development
Thanks for your response.
Actually, the problem is not about checking if the source Field2 is NULL, but to insert NULL in the destination table. For some reason, the NULL value of Field2 is converted (I think) in something that is neither NULL nor a LONG in the INSERT INTO statement.
Regards.Matteo, .NET Developer and System Engineer
-
Friday, April 27, 2012 7:52 AM
I could not see the pictures yesterday (maybe bacause I was behind a firewall then?)
anyway, maybe in the dataflow make a conditional split on the values with field2 being null then lead them to a destination oledb where you only insert field1
the other exit of the condition then uses the oledb destination as in your example where it inserts both fields. it is not beuatifull but it will work. If the customer is waiting for a solution, then i say "à la guerre comme à la guerre"
Jan D'Hondt - Database and .NET development
-
Friday, April 27, 2012 8:49 AM
I could not see the pictures yesterday (maybe bacause I was behind a firewall then?)
anyway, maybe in the dataflow make a conditional split on the values with field2 being null then lead them to a destination oledb where you only insert field1
the other exit of the condition then uses the oledb destination as in your example where it inserts both fields. it is not beuatifull but it will work. If the customer is waiting for a solution, then i say "à la guerre comme à la guerre"
Jan D'Hondt - Database and .NET development
Hi jandho,
This is a nice idea, but I cannot do it that way because my real destination datatable has many more fields that can be NULL (the source datatable in this post is just an example ). Because there are a lot of combinations that can come from the source, I cannot use conditionnal splits...
I was thinking of expressions, but I don't know how I can use expressions in this case.
Regards.
Matteo, .NET Developer and System Engineer
-
Tuesday, May 01, 2012 12:38 PMHi guys!
I am still in trouble with this issue...
Anyone to help me ?
Thanks.
Best regards.Matteo, .NET Developer and System Engineer
-
Tuesday, May 01, 2012 4:23 PMAs oldjeep suggested, did you try to change the data type from LONG to VARCHAR or NVARCHAR (DT_STR/DT_WSTR)?
-
Wednesday, May 02, 2012 8:28 AM
As oldjeep suggested, did you try to change the data type from LONG to VARCHAR or NVARCHAR (DT_STR/DT_WSTR)?
Hi,
Yes, but this does not change anything, because it's not a "null" value.
Regards.
Matteo, .NET Developer and System Engineer
.png)
