I am new to SSIS and am trying to change a current process that moves data from one server to another using a linked server to use an SSIS package instead.
Currently, I have am getting a list of the 160 tables and setting the output to an Object variable. Then I am using a Foreach Loop Container to iterate through each of the 160 tables along with a Data Flow Task to move the data from the source to the destination.
The part I am having issues with is that some of the 160 tables have schema differences i.e. the destination table does not have all of the columns contained in the source table.
For each table, I can get a list of the columns from the destination table and assign the output to an Object variable but I'm stuck at that point and am not sure to proceed conceptually.
So far, I have eliminated the option of converting the Object variable into a string by using an expression. I read that expressions can't handle Object variables.
I was thinking of using another Foreach Loop Container to iterate through the column names in an effort to build a comma-separated string which I could use in an expression to turn into a SELECT statement but I'm having difficulties building the expression to concatenate the column names to same string variable.
Does anyone have conceptual suggestions on how I can solve the schema differences issue?
I am developing the SSIS package inside of Visual Studio 2013 for use on a server running 64-bit SQL Server 2014 Standard Edition.
Best Answer
I was able to solve my problem by using the information on this site:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/70875/
This showed me how to build a comma-delimited string from an Object variable by using a Foreach Loop Container.