SSIS DataReader Source Task
SSIS ETLers,
If you‘re looking for a way to assign an expression to the SQLCommand property of the DataReader Source task , you can do that from the properties of the Data Flow task that contains the DataReader (Not the DataReader itself)
I wanted to share coz it was painful for me to figure it out!
11 comments:
Thank you Natasha... I was having a real problem with this!
I also found that I had to set a 'static' version of the string in the SQLCommand property so that the downstream data flow was generated properly - the UI was "ignoring" the version in the expression editor
You are welcome:) I do that as well , but I remove it later on…
Wooooo.. nice tip Natoush :)
Here's one for you: Why use a DataReader Source rather than an OLEDB Source.. why/why not?
Good tip however I am struggling to get this to work. I defined a variable in my workflow which get's populated. I then attempt to use that variable to build my expression in the SQLCommand property but the variable always defaults to 0. Has anybody suffered from this problem?
Ammar,
When you try to evaluate the expression, it will definitely be 0 or empty, maybe you are not populating it properly (how are you populating your variable?), did you track the value you are trying to assign?, you can use a msgbox or a derived column with a data viewer after it , if you did and the value is ok, maybe you are doing string concatenations in your expression and you have special characters or something like that, here is an example of a well formed expression for a connection string :
"SELECT Col1, Col2, Col3
FROM Table1
WHERE (DTM > '" + @[User::MyDTM] +"')"
Kilani,
Sorry for being extremely late, I used the data reader because I wanted to connect using a native oracle connection, and that doesn’t work with OLEDB data source.
However after that experience, I value OLEDB more, it is the oldest, most stable and best tested :)
Hi Natasha,
Sorry for the lateness in my reply but I hasn't realised that you repli3ed to my question!
Anyway, here is what my SSIS package does :
I have a an empty table called Test1 (with an ID column called result_id)
I use an SQL Query Task to retrieve the maximum result_id and place it in a user variable called @max_result_id
I have used a break point and the locals windows and it's definitely picking the maximum result_id every time so I know this bit works just fine.
In my data flow task I used a data reader source, I used the following :
"select * from result where (result_id > " + (dt_str, 10, 1252) @[max_result_id] + ")"
which evaluates to :
select * from result where (result_id > 0)
But as I said, the value of my variable always seems to evaluate to 0 even though it is set to the maximum result_id before the package enters the data flow component.
Any ideas?
Hi again,
I have solved the problem. It appears that I had another variable defined at the component level which was overriding the original variable which has a Packe scope.
It is working perfectly now.
Sorry for the bother!
Hi..
Please can you help me by explainning in detail how can i pass parrameter or expression in DataSource Reader using ADO.Net..
i have 2 query..first query use oled connection to fine a value from query.
2nd is ado.net connection which has query that use the value that finds in 1st query ..but i dont know how can i pass the value from first query to second query in DataSource reader..
please can u help me to solve this problem
Great tip! Thank you and thank the internet and shame on the SSIS-developers
Genial post and this enter helped me alot in my college assignement. Thank you for your information.
Post a Comment