August 22, 2007 at 2:52 pm
I have a lot of SQL2000 DTS packages that keep track of the last date/time they queried a foreign table (linked server), and which set a DTS package variable that in turn is placed into the SQL string that is executed the next time it runs.
This allows me to query the source data for all records that have been modified since the last time I queried.
I am trying hard to find out how to do this in SSIS, but something that used to be so simple seems to be absolutely buried now. I've found the variables in SSIS, but how do I set and retrieve the value in there at runtime without resorting to .NET programs accessing the SSIS object module? Surely this can't be as hard as it is appearing?
August 23, 2007 at 11:23 am
If you store last date queried in a table, you can use a SQL Task to query that table, retrieve the value and assign it to a variable. You would then map that variable to a parameter in another SQL task that queries your source data.
Another option would be to use the XML Task to read/write the date.
Another option would be to use SSIS Package Configurations to set the variable;though updating these may require some effort.
Personally, I'd go with the first option.
You cannot set a variable during runtime and persist it within the SSIS package itself. I've read that Microsoft made a decision to prevent self-modifying packages, so nothing that occurs during runtime can be saved back into the SSIS dtsx file.
hth,
Ed
August 23, 2007 at 2:20 pm
Thanks Ed,
Indeed, I was hoping to do exactly what you suggest in the first option - read from a table, use the date, and update the table at the end of the package. What ended up being the fly in the ointment for me was that my source connection had to use the DataReader ADO.NET provider.
This won't allow you to map parameters the way you can with an OLE DB connection. A bit more looking trying different keywords in Google found an MSDN forum post with the following solution:
Create a SSIS variable to hold the query;
Set the EvaluateAsExpression property of the variable to true and provide an expression to build the query.
Then in control flow;
Select the dataflow that contains the datareader;
Go to properties and select the Expressions property.
There, you can assign the sql statement of the data reader via expression;
Then you just have to create an expression that points to the variable that hold the query.
In addition to the variable/expression; you need to 'hardcode' a query inside of the darareader; that is how BIDS gets the medatada at design time. That hardcoded query will be overwritten by the expression at run time.
The whole thread is at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2009522&SiteID=1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply