data reader source in data flow problem

hi all,

i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following:

how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example:

select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it.

please help,

tomer

You can set an expression on the SQLCommand property. Similar to as explained here: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

The difference with the datareader source is that you cannot use a variable. Instead, set the expression on the SQLCommand property via the expressions of the parent data-flow.

-Jamie

|||

hi jamie,

thank you for your advice but i still did not understand completely.

the example u sent me is with ole db data source. lets say i have created the variable

and put there the select. now how do i config the data flow to use it?

can you give me another example?

thx,

Tomer

|||

Like I said, the Datareader Source does not allow you to use a variable as input. So, instead of putting the expression that builds your SQL statement into a variable, put it in an expression that sets the SQLCommand property of your Datareader Source. The interface to setting this expression is in the properties pane of the Data Flow task in which the Datareader Source resides.

-Jamie

|||

hi again Jamie,

i have put there:

"select * from x where y=" + @.[user::max_date]

and i get an error.

can u advice?

|||

Is max_date a Datetime variable? If so you need to cast it as a string in order to be able to concatenate it.

-Jamie

|||

Jamie,

thank you very much for your help - you realy helped me here and saved me lots of time.

tomer

|||

Hi Jamie,

yesterday everyyhing worked fine and suddenly i get in the package VS_BROKEN?

why can u advice me on that please?

tomer

|||

You have made a change in one of your components that means it won't work anymore. It should tell you which component.

-Jamie

|||

it says its failed executing the SQL command at the data reader source and i get validation error VS_BROKEN/ my sql is like yesterday:

"select * from x where y = " + [user::x]

when x is a string type. i must have changed somthing small without noticing...?

|||Are you missing the @. before the variable name (should be "@.[user::x]" ) in your sql command, or is that just a typo in your post?