October 16, 2013 at 7:31 am
I am having an issue using a variable in a SQL Command used in my OLE DB Data Source.
I am pulling values from a table, using COALESCE, and placing the retrieved data in a format that could be used in a "WHERE IN ( ? ) " statement. However, it is not acknowledging the variable.
The variable is being set correctly, and is in the correct format (I can see this in the locals view). However, each time it runs, it is just ignored and pulls no rows of data.
I have also attempted to write the query as an expression, and passing the variable within the expression, with no luck.
If I change the code to use "= ?", and hard code a value in the variable, it works just fine.
So, I guess my question is, can this be done? All over I see examples of variables using the "=" qualifier, but cannot find any examples in "in".
Any help would be appreciated.
Thanks
October 16, 2013 at 7:34 am
It should work when you create the SQL statement in a string variable and use that variable as the source for your SQL statement in the OLE DB source.
Make sure the EvaluateAsExpression property of the variable is set to true.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2013 at 8:11 am
Thanks Koen. I have gotten a little further.
When I chose a variable where I hard code the same value I am creating as the variable I wish to use, the command does work as an expression using an IN qualifier.
However, when I change the expression command to use the variable that is empty prior to the package running, I get the following error when I assign the SQL Command variable.
An OLE DB record is available. Source:”Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E14 Description: “Incorrect syntax near ‘?’.”.
It appears the SQL command from variable cannot validate the variable used in the where statement, because the value is not set prior to run time, and the SQL command needs it? I have delay validation set to true on the data flow task this command is being run in, and that did not seem to help either.
October 16, 2013 at 8:22 am
Please will you post the value of your variable (in other words, the command you're trying to run)? It may be that there's another way of doing it. One thing that's worth considering is, given that you're building the command from the variable anyway, you could build the whole finished command in that variable, to include the parameter values, so that you don't need to use the "?" placeholders. Another thing you might try is using an ADO connection instead of OLEDB. Your placeholders would then be in the form "@MyVariable" instead of "?".
I hope I've explained that clearly. If not, I'm happy to have another go!
John
October 16, 2013 at 8:28 am
John, building the variable as the query containing the values is a decent idea, and may in the end decide to go that route. However, this seems like it should be so simple, I almost feel like I can't let it defeat me.
I attached 2 queries. One that I am using as a SQL command as a variable, and the second would be just a SQL command passing in the variable in the OLE DB Source.
Thanks for your help and suggestions.
October 16, 2013 at 8:44 am
The second one definitely won't work. It's not valid syntax. Try this - it doesn't work:
DECLARE @string varchar(100)
SET @string = '''A'',''B'''
SELECT * FROM sys.objects
WHERE name IN (@string)
I think the correct way to do this is to dispense with the comma-separated list altogether and use a data flow. You can either use a merge transformation, or you can import the values you need from the table into a staging table and then join that to your destination table to do the update.
John
October 16, 2013 at 12:56 pm
mike.hockman (10/16/2013)
John, building the variable as the query containing the values is a decent idea, and may in the end decide to go that route. However, this seems like it should be so simple, I almost feel like I can't let it defeat me.I attached 2 queries. One that I am using as a SQL command as a variable, and the second would be just a SQL command passing in the variable in the OLE DB Source.
Thanks for your help and suggestions.
Do you have BIDS Helper installed? If not, please install it. You'll get the Konesans expression builder, which you can use to build the expression for the variable. You can evaluate the expression. The result should be a valid expression you can run in SSMS.
I would fill in a valid value for the @[User::varDIST_SFA_ID] value (which is a string variable I suppose). The OLE DB source will validate the query to get the columns metadata, so if it is empty you will get an error. Doesn't matter if DelayValidation is on, that setting is used during runtime, but not during design time.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 16, 2013 at 1:53 pm
The issue turned out to be the blank string variable at run time. As soon as I provided an 'xx' as the value for the variable, the package runs, sets the variable correctly, and they query runs as expected.
Thank you everyone for your help!
October 16, 2013 at 1:54 pm
mike.hockman (10/16/2013)
The issue turned out to be the blank string variable at run time. As soon as I provided an 'xx' as the value for the variable, the package runs, sets the variable correctly, and they query runs as expected.Thank you everyone for your help!
Great, glad that you got it working!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply