Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS (2012) - Using Variable as "where in (?)" Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 7:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:55 AM
Points: 38, Visits: 432
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
Post #1505186
Posted Wednesday, October 16, 2013 7:34 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 12,206, Visits: 9,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505191
Posted Wednesday, October 16, 2013 8:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:55 AM
Points: 38, Visits: 432
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.
Post #1505225
Posted Wednesday, October 16, 2013 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Post #1505243
Posted Wednesday, October 16, 2013 8:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:55 AM
Points: 38, Visits: 432
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.


  Post Attachments 
SQL_command_as_variable.txt (17 views, 1.43 KB)
SQL_command_using_variable.txt (14 views, 1.40 KB)
Post #1505253
Posted Wednesday, October 16, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Post #1505275
Posted Wednesday, October 16, 2013 12:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 12,206, Visits: 9,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505385
Posted Wednesday, October 16, 2013 1:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:55 AM
Points: 38, Visits: 432
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!
Post #1505407
Posted Wednesday, October 16, 2013 1:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 7:29 AM
Points: 12,206, Visits: 9,168
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1505408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse