Copying tables using SSIS package

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

SBowe wrote:

I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.

I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.

I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.

I'm wondering if I need to do a For Each loop looking at an ADO object?

Any suggestions?

Stephanie

I suggest you use the Import Wizard to do this for you. I think you can configure it to build the tables for you if they are not already there.

-Jamie

|||

Jamie,

Thanks for the suggestion. However, that won't work for my environment. Specifically, my company requires that I create a scheduled job. So I then must utilize a package.

Here's where I get the flaky results using the SQL transfer object: I only want the non-system tables. So I set the All Tables property to False and then select the tables I want from the Tables Collection property. However, the package then fails when I run it and tells me it cannot find the tables from the source. This is mind-boggling since it allowed me to pick the tables from a list of tables.

I'll keep digging.

Thanks again,

Stephanie

|||

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

|||Stephanie,

I would advocate NOT using the transfer objects task.

This has some "features" (apparently to preserve sql 2000 compatability) that means the tables will not be transferred over accurately.

Specifically, you may find your transferred tables lose default values or identities

see my thread on this here|||

Rafael Salas wrote:

Stephanie,

If you use the import/export wizard in SSMS todo this; you can choose to save it as an SSIS package; then you can open that package and make the specific changes you need (eg renaming the 4 tables).

Rafael Salas

The import / export wizard will also not setup the tables correctly - see the post I linked to in my post above