SSIS 2008 - ADO NET SOURCE, no parameters!
Are they joking?As from now as a developer, whenever you need to read data from an ODBC source (and thus your only option is to use the ADO NET Source component in a data flow task - no ODBC data source anymore), you are in big trouble when you want to use ugly beasts like parameters. Parameters? Who needs them anyway they must have been thinking at Microsoft SSIS development team.In 2005 you had the option "SQL from variable" in combination with expressions, which was a quick and dirty way to force parameter insertion, but now, since they just gave up on that option, you're inbig ... as a developer.I hope I am terribly wrong here, could somebody please help me out on how to pass parameters to an ODBC data source in SSIS 2008??Thanks in advance,A frustrated Tom with a deadline in mind Tom De Cort - BI Consultant
February 3rd, 2009 5:15pm

You can use expressions to set the statement in an ADO Net Source - you just have to do it from "outside" the Data Flow. Go to the control flow tab, select the Data Flow that contains the ADO Net Source, and look at the properties window. You'll see the properties for the ADO Net Source there, as well as an "Expressions" property, where you can set the expression to generate your dynamic SQL. Definitely a PITA, but it is doable.
Free Windows Admin Tool Kit Click here and download it now
February 3rd, 2009 9:26pm

Hi Todd,Thank you very much, this solved my issue (and a lot of time). Though I still think they should have changed this in 2008.Kindregards,Tom Tom De Cort - BI Consultant
February 4th, 2009 11:50am

Hi Todd, Thank you for your help! Kind Regards, Johan
Free Windows Admin Tool Kit Click here and download it now
June 15th, 2010 3:14pm

I am having the same issue in trying to pass a package parameter to a Data Flow Task to populate a table. Could you detail the steps involved in this process? Thanks!
August 2nd, 2010 7:15pm

How do you pass params to an ADO Net Source in a OnError handler inside a Data Flow Task? Doesnt seem to work.
Free Windows Admin Tool Kit Click here and download it now
February 24th, 2011 11:13pm

The same way you do for a regular ADO Net Source - use expressions. Click on the background of the Data Flow Task, wherever it is, and make an expression on the SQLCommand property. Talk to me now on
February 25th, 2011 12:55am

Did that inside a Data Flow in a On Error event handler and the expression would not work. The same exact expression works outside of a On Error handler however... inside a data flow...
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2011 9:58pm

What does "would not work" mean? Did you get an error? Talk to me now on
February 25th, 2011 11:29pm

I tried this and actually it gave me error on ok SELECT '787'+RIGHT([No],9) AS Number FROM ( SELECT [Numb] FROM [table] WHERE column=@[User::Var] ) DRVTBL TITLE: Expression Builder ------------------------------ Expression cannot be evaluated. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: Parsing the expression "SELECT '787'+RIGHT([No],9) AS Number FROM ( SELECT [Numb] FROM [table] WHERE column=@[User::RegId] ) DRVTBL" failed. The single quotation mark at line number "1", character number "8", was not expected. (Microsoft.DataTransformationServices.Controls) ------------------------------ BUTTONS: OK ------------------------------ What can be wrong ? and how to solve it ? What should i put here?!
Free Windows Admin Tool Kit Click here and download it now
June 12th, 2011 9:15am

Hi, Please try this expression "SELECT '787'+RIGHT([No],9) AS Number FROM ( SELECT [Numb] FROM [table] WHERE column=" + @[User::Var] + ") DRVTBL" Thanks Sam
June 12th, 2011 1:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics