Copying DFT

Question

Hi All,

I have an SSIS package that has one DFT. In that DFT, am basically loading tables without any transformation. All the tables will be truncated before loading using an execute SQL task which is in the control flow. This DFT is for one of our entities. Now, we have to do the same for 10 other entities and the tables are exactly the same but the database names are different(each company has a different database name). I thought this is easy and I just copied the DFT and created a new connection for second database and pointed the OLEDB Source/Destination to the connection, but the issue is that as soon as I do this, I’ll need to go to the list of tables and select the table again(which is cumbersome as we have 100s of tables).

                                                                                                                                                                                                                                                       

Any idea how to do this without having to go into each OLEDB Source/Destination and selecting the table again?

Answer 1

Create the package programmatically.

Or you can simply create a BCP script, or a SQLCMD with OPENROWSET wrapped into a .BAT file, you name it.

There are at least 6 ways to move data with SQL Server after all.

---

Arthur My Blog

Answer 2

And you think what you suggested is faster than manually changin the OLEDB Source/Destinations?

Answer 3

For my understanding you have the same table names and schema, but many databases in the same instance. For that I will use a foreach tasks which will be for each database. You can generate your database names and loaded into a object variable with a SQL task, then use the object for the foreach loop. The foeach loop will change the connection string of the connection manager you are using. That will let you use a different databases.

Answer 4

You don't need to create separate DFT for each of your database. You can dynamically modify the OLE DB Connection Manager to point to a different database at runtime. You have to setup your DFT inside For Each Loop and do the dynamic connection string modification there.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 5

You can handle it with package configurations as well, by setting a configuration item to the connection manager's ConnectionString property, then executing the same package twice, with different configurations (pointing to different database).

But, if you don't want to use a loop, what about copying the whole package, and setting the connection manager's ConnectionString? (minimal effort :))

Regards,

Zoli