SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS (2012) - Using Variable as "where in (?)"


SSIS (2012) - Using Variable as "where in (?)"

Author
Message
mike.hockman
mike.hockman
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 595
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65002 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mike.hockman
mike.hockman
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 595
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.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35960 Visits: 16699
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
mike.hockman
mike.hockman
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 595
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.
Attachments
SQL_command_as_variable.txt (74 views, 1.00 KB)
SQL_command_using_variable.txt (62 views, 1.00 KB)
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35960 Visits: 16699
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
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65002 Visits: 13298
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.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
mike.hockman
mike.hockman
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 595
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!
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65002 Visits: 13298
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!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search