0

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.

Improve this question
1
  • This may sound tedious, however I highly recommend building a package for each table and putting them in a sequence container that calls each package. This will make it so 1 failure doesn't hose your entire load and allow you to run multiple packages at a time thus significantly speeding up your load. It will take a bit longer and require more individual work. However straight load packages are easy to make and will allow you greater control over this processing.
    – Zane
    Sep 6, 2017 at 18:45

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.