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

Copying multiple SPs in SS 2005

I am totally stumped. In SQL Server 2000, I would fire up my EM and right-click on multiple SPs and then Generate Scripts. I then would start QA and run the script on a different DB. This was a very convenient feature to copy SPs over from one DB to other as well as from one machine to other. I can't seem to do the same in SQL Management Studio. Is it possible? If yes, how?

Now this may not be the right place to ask but since VS and SS go hand-in-hand, I thought I would ask.

If you right click on the database -> Tasks -> Generate Scripts you can select the sprocs you want. I know what you mean though, that exact functionality seems to be missing. If you click on procedures and then go to the summary window you can multiple select but can't script from there. Also, you can right click on each sproc and script to clipboard if you want.
Hth,
Scott|||According to Microsoft's SQL Team:

>> Multi-select script didn't get in till recently – its in post-IDW15 builds and will be in the next public CTP of SQL tools.