SSIS Scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS Scripts

Author

Topic

teg
Starting Member

14 Posts

Posted - 2010-01-25 : 09:40:08

Hi,

Im trying to upgrade an old DTS package that uses the date programitically
to load a table from an AS400 database.

Even though the table name changes (Based on the date), the schema of the
tables are all the same. I used to use Activex scripts to achieve this in DTS
and worked great, but I am struggling to get this working with the new SSIS
object model. Does anybody have any example code for this? Its just the Sql
command text that I need to change.

I am forced to use ADO.net connection object as I cannot get the OLE to
work. Otherwise I would try and use variables.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 09:41:39

you can easily achieve this using expression builder in ssis and mapping to source property of file

teg
Starting Member

14 Posts

Posted - 2010-01-25 : 09:48:51

The date the file needs to load is stored on a sql server. the active x script runs an SP to get the date. and the changes the sql based on that date.
Can you forward code to do this in Expression builder? Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 09:51:58

quote:
Originally posted by teg

The date the file needs to load is stored on a sql server. the active x script runs an SP to get the date. and the changes the sql based on that date.
Can you forward code to do this in Expression builder? Thanks

does file name has date it needs loaded info?

teg
Starting Member

14 Posts

Posted - 2010-01-25 : 09:53:43

Hi Sorry, can you rephrase?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 09:55:09

quote:
Originally posted by teg

Hi Sorry, can you rephrase?
Thanks

does file has as data the date value which determines when it is to be loaded? or does filename has that?

teg
Starting Member

14 Posts

Posted - 2010-01-25 : 10:00:39

Its actually a table on the iseries. For example. DWH.T1100121 (library.table).
We discover the last loaded date from the datawarehouse and derive the next file to load. For instance. The Datawarehouse will state we have loaded the file for 20th Jan 2010 (T1100120) and we need to load the 21st Jan (T1100121). But sometimes it could be 5 days worth. This is why I use a For Loop to loop through all the days in between. If only I could change the SQL on the fly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:04:50

quote:
Originally posted by teg

Its actually a table on the iseries. For example. DWH.T1100121 (library.table).
We discover the last loaded date from the datawarehouse and derive the next file to load. For instance. The Datawarehouse will state we have loaded the file for 20th Jan 2010 (T1100120) and we need to load the 21st Jan (T1100121). But sometimes it could be 5 days worth. This is why I use a For Loop to loop through all the days in between. If only I could change the SQL on the fly.

isnt it enough to store last date value in a variable and then compare to it and then take next one which is greater than it?

teg
Starting Member

14 Posts

Posted - 2010-01-25 : 10:07:23

I can get the last val in a variable, but can't get it to change the SQL on the fly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:18:22