Wednesday, March 7, 2012

Data migration overview help

Hello,

This is my first project with SSIS and I'm having some problems migrating our current data import to it. Generally: right now we're pulling hourly data (which has a timestamp on it) in from a customer's db(which can't be modified, and is big and growing quickly) into our db. To do this we have been using a field to keep track of the last data download time (in our db) and then using that then when querying the customer's db to reduce the number of rows returned.

Will a similar system work in SSIS? I'm having trouble pulling the last download date out of the "target" database and using it in the query on the customer's db. Any advice on what I should be looking into to do this and am I headed in the right direction or just not thinking about this in a SSIS way?

How powerful/common are variables in SSIS and should I be trying to use them here?

Thanks,
Jeff

Variables are the way to go with this. Use an ExecuteSQL task to grab the timestamp and store it in a variable. Then either use the variable as a parameter on your data source SELECT statements in the data flow, or build the SQL dynamically, using expression based variables. Generally, if your data source is not SQL Server, it seems like you are better off using the expression based variables.

No comments:

Post a Comment