January 18, 2011 at 4:30 am
I have a source table with NULL values for some columns. My package structure is as follows -
1. I have created package level variables - each with data type object.
2. Create Execute SQL task to select rows from source.
3. For each loop to loop over rows and assign respective variables for respective columns using index column in variables mappings tabl.
4. Inside for each loop, i have one execute sql task to insert rows in destination table using those variables.
My problem is that, the execute sql tas inside for each loop fails for NULL values. It transfers only those records for which no column has null value.
(NOTE: I know i can achieve the same thing in a simpler way using a simple data flow task. But this is the simplified version of my actual complex scenario and I will need for each loop and variables and execute sql tasks in actual scenario.)
January 18, 2011 at 4:36 am
You say it 'fails' - so I am guessing that you get an error of some sort. Please post the text of this error message.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2011 at 4:39 am
I guess you can handle this case just by handling the NULL values in Execute SQL task.
Abhijit - http://abhijitmore.wordpress.com
January 18, 2011 at 4:50 am
The task did not fail , but it transferred only those records for which no column contains Null values.
January 18, 2011 at 4:52 am
Abhijeet,
How do i handle Null values in Execute SQL task?
January 18, 2011 at 4:58 am
Perhaps you could post your SQL.
If there were no errors, the chances are that the data was not even hitting the database - so perhaps you have WHERE conditions which need to be modified to accommodate NULLs. It's difficult to tell without seeing what is causing the problem.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2011 at 5:08 am
Ok, I can see some error now in progress tab -
[Execute SQL Task] Error: Executing the query "insert into Emp_Copy(id,EmpNo,EmpName) values (?,?,?)" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is the data in my source table
idEmpnoEmpName
1111AAA
2112NULL
3NULLCCC
It transferred only first row
January 18, 2011 at 5:25 am
What is the ResultSet property for the task set to?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 18, 2011 at 9:08 pm
For the first ExecuteSQl task, the result set property is set to "Full Result Set".
For the second ExecuteSQl task, the result set property is set to "NOne".
January 19, 2011 at 12:32 am
I bet you've guessed my next question already: please post what the parameters screen contains - perhaps even a screen shot if it's not confidential.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 19, 2011 at 1:59 am
Image Attached
January 19, 2011 at 2:08 am
Image Attached...
January 19, 2011 at 2:32 am
It looks like you've got the same issue as this guy.
Based on the fact that it does not look like it will be very easy to solve with this approach, the next thing I would try is building your INSERT query using Expressions, rather than parameters. If you do that, you'll have more control over the T-SQL syntax and should be able to introduce some CASTs into the expression which may get you over this hurdle.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 19, 2011 at 8:45 pm
And how do i do that????
Help pls.............................. 🙁
Isn't it frustating??
January 20, 2011 at 12:25 am
Have a look here.
Use an expression to set the SQL string which will be executed by the task.
The expression can contain a combination of literal text and variables (system and user).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply