Dynamic column mapping in ssis

• VinceM

  SSC Veteran

  Points: 279

  More actions

  February 4, 2014 at 1:27 am

  #283124

  Good Day,

  I am new to ssis, and working on archiving data from Teradata to sql, and I am having problems with mapping columns dynamically from source to destination. Details of what I have done so far are here:

  1. I have more than 500 tables that I need to move from teradata to MS Sql

  2.I have created the following: A spreadsheet to list all table names in teradata that I will move to SQL,

  - I have a data flow task: within the data flow task I have a flat file source that reads table name from CSV I am storing the table names on a variable called All_tabls.

  -I also have a for each loop container. Within my loop container I have Execute sql task(build ArcTbl)- inside this I call a stored proc that will dynamically build a table in MS sql the way it is in Teradata. datatypes in Ms sql have all been changed to varchar in msSql.

  -The second Execute sql Task(Getselectstatement): This calls a stored proc that dynamically build a select stament, input parameter here is @tableName, and the output is the entire select statement to be used in teradata to select data, this i stored it on the variable"Select _stmnt"

  -I have the data flow task(Source Dataflow task)- Inside this is Teradata source, connection is mapped to Teradata, and using SQL command-TPT Export(Select 1- which is the value i set for select_stmnt)

  Up to this point everything works well, now I have a problem with the following:

  1. I want to add a script component(destination) and read data from Data flow(teradataSource) to destination table(Dynamically created table in ms sql in step 2 above. My problem is getting the right C# code to dynamically read data and load it to target.

  Any help with this will be highly appreciated.

  Thanks

  VinceM