Hello all, I'm missing something here and I was hoping I could get some help. We are putting some records into an ADO recordset. Reproduction is as simple as a two column, one int, one varchar(50). Load one row into it with 5 and then NULL for the varchar. Based on articles like http://www.developerdotstar.com/community/node/338I see that even though the meta data says it's a dt_str going into the recordset, when it's getting pulled back out, if it's nullable, it needs to be a datatype of Object. We are trying to use those variables as parameters to an Execute SQL Task and therein lies the rub. The true query is much more complex but for reproduction sakes, assume it's Code SnippetINSERT INTO dbo.MyTable (row_id, col_value) SELECT ?, ? I am using the Native OLE DB\SQL Native Clientconnection manager and it's talking to my local database. Nothing fancy there and I've used this method before so I feel comfortable that this is set up correctly. I have my parameters as User::row_id and User::col_valuebutthe parameter mapping blows up with "[Execute SQL Task] Error: Executing the query "INSERT INTO dbo.MyTable (row_id, col_value) SELECT ?, ?" 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. " And that vaguely makes sense since the locals viewer will show my object variable as a type of System.DBNull So, what do I do to make that value get into my database? Using the proper type for the variable is right out as the foreach loop doesn't handle the null (Error: The type of the value being assigned to variable "User::col_value" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Create a second variable and use an expression based onthe Object versionto make a correctly typed variable. I proved that I can make a null get into my database for all cases by simply assigning it as (DT_WSTR, 5) NULL(DT_STR, 5, 1252). However, trying to make it use the above expression conditionally fails. Expression cannot be evaluated. & The data type of variable "User::col_value" is not supported in an expression. I tried a script task to do the same thing as step 2 but "The type of the value being assigned to variable "User::col_value3" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object." Next up is to try a different provider but I don't know if that will work any better as well as curling up with BOL. So what's the solution? I thought about using an expression to create the SQL statement but I suspect I will run into the same situation in 2. A more hackneyed derivative would be to put a sentinel value in the nullable column and then build my sql statement doing the appropriate null substitution there but ick, that just feels wrong. Any and all help, insight, winks, nudges, links, cracks over the head with a hammer, etc would be greatfully appreciated

Hey Charles, I didn't follow a large part of the details there, but to me, it doesn't look like a problem with pushing data into SQL, it looks like there's a problem getting data out. Your error states that "An error occurred while extracting the result..." - but an INSERT only returns a scalar. I can see you've used this stuff before (likely more than me) - but is it possible that something's (mis)configured to try to put data into a variable (by result set or output parameter) rather than just stuff things in? If you really had a problem with stuffing things into SQL, I'd expect a SQL style error, not an SSIS "variable type" style one.

Howdy Todd and thanks for the thoughts. I'll see if I can get a minimal reproduction out there but the short answer is that I can'tfigure out how to pass a null value in to a string data type using an OLE DB provider on an Execute SQL Task. However, setting up a nearly identical SQL statement with the ADO.NET provider and using the same parameters typed the same as my original just worked fine. Different between the statements btw is justthe provider specific placeholders in the statement ? vs @paramName.

I think you mentioned this in the things you'd already tried, but have you tried this? Create a boolean variable that stores whether or not your string is null. Then, in your SQL statement,use a case statement, like this: Code Snippet INSERT INTO dbo.MyTable (row_id, col_value) SELECT ?, CASE ? THEN NULL ELSE ? END Where the first "?" is your ID, the second is the "isnull" boolean variable, and the third is your string variable?

The challenge at least as I'm running in to it is that I can't touch a variable of type Object in an expression, therefore I can't figure out how to set a boolean based on the result of an IsNull call. I think this should be a working reproduction of my what I'm attempting to do. Obviously, my real case is more complex but it should demonstrate how I am attempting to assign a parameter using an OLE DB connection manager vs an ADO.NET It will blow up if the OLE enumerator is enabled and work just fine if it's disabled. http://cid-84dfa4294693ec43.skydrive.live.com/self.aspx/SSISForums/NullStringVariables.dtsx This does create a table and drop a table in your local master database so if that leaves the reader uncomfortable, point the CMs to an appropriate location. Thanks again all

Charles - I don't have 2005 installed on my current dev system. I have 2005 on a VM, but it's not easily accessible at the moment.I'll see if I can get that loaded up in the next day or so...

I am facing the same issue mentioned here... Any updates on this by now??