Upgrade DTS to SSIS using script

[teg]

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

[Todd C]

Try this:
Create a String type variable and seed it with a valid SELECT statement that
you would execute against the AS400 Connection Manager. In this example, I'm
naming it "SQLCommand". You will also have a Date variable named "TableDate"

Put in a Script Task above the Data Flow and specify SQLCommand in the
ReadWriteVariables list. Also set your TableDate variable in the ReadOnly
section. Edit the script to build the SELECT statement dynamically:

Dim sql as string
Dim TableName as string

TableName = Dts.Variables("TableDate").Value.ToString

sql = "SELECT ... FROM " + TableName + " WHERE ..."

Dts.Variables("SQLCommand").Value = sql

Now set your Source Adapter on the Data FLow to use SQL Command from
Variable and select SQLCommand as the variable.

Couple of notes:
*Variable names in Script tasks are case sensitive.
*The SQLCommand variable needs a valid SELECT statement in it so the Data
Flow can get proper meta data for design purposes. Make sure you reference a
table that contains the correct meta data.
*I'll leave it to you to format the TableName in the script properly so it
matches an actual table name. The value of the variable might be "2010-01-27
05:24:16" and you might need it in the format of "01272010" or something else
to match your table naming rules.
*Experinemt with a variable's property EvaluateAsExpression and it's
Expression property if you want your Date variable to by dynamic at run-time.

HTH
--
Todd C
MCTS SQL Server 2005

Post by teg
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

[teg]

Thanks Todd, thats great.

Once I worked out I need to use the Data Flow Expressions found in
properties this worked.