Best way to import tables from DB to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Best way to import tables from DB to another

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-26 : 10:32:32

I have a database with a set of tables created every day to be uploaded with that days data:

Table1_20090101
Table2_20090101
Table3_20090101

Table1_20090102
Table2_20090102
Table3_20090102

Table1_20090103
Table2_20090103
Table3_20090103

Only a set number of days worth of data is retained. Once this threshold is reached, then the oldest set of tables have to be imported into an archive DB.

Issue:
The tablenames are dynamic, therefore, I need to be able to get all the table names dynamically, and then select out the data.

What is the best way to do this in SSIS (bearing in mind I am a complete novice)?

So far, I've selected the table names in to an ADO.Net dataset using a script task. Thats as far as I have got.

What tasks can be used for such a job? How do I dynamically reference tables (do I iterate through the ADO.Result set, and somehow generate a select statement??)

Any help will be appreciated.

Thanks

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-02-26 : 10:53:18

If you are archiving them to a DB on the same server or on a linked server, there is no need to use SSIS, you could have a sql job that gets the final table in the threshold and simply select into an archived table, you can run this as often as you need to then.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-26 : 10:55:09

In summary, I want to select a table from one database, get the name on the fly, and import into another database as is (so same table name, format, etc... like a select INTO).

There could be as many as 8 million rows per header table, so whatever method has to be fast. This could have to occur across DB servers (don't know yet).

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-26 : 10:58:30

Hi RickD

Thanks for your reply.

So I could have a series of SELECT * INTO statements.....

Would this differ in performance to using SSIS? Or is it a case of try and see......

Thanks

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-26 : 11:49:18

ok, I now have the TableNames populating a result set, then I read each table in a foreach loop. I use a data flow task to select the variable name. I want to insert into a new table of the same name??

How do I do that?

Also, when I execute the package (and for the time being, insert into a random newly created table), I get the following error:

Warning: 0x800470C8 at Data Flow Task, OLE DB Source [146]: The external metadata column collection is out of synchronization with the data source columns. The column "RecordSeqNo" needs to be added to the external metadata column collection.
The column "RecordDateTime" needs to be added to the external metadata column collection.
The column "ArticleID" needs to be added to the external metadata column collection.
The column "EntryMode" needs to be added to the external metadata column collection.
The "external metadata column "ReceiptRePrintedFlg" (302)" needs to be removed from the external metadata column collection.
The "external metadata column "VATReceiptPrintedFlg" (299)" needs to be removed from the external metadata column collection.
The "external metadata column "GiftReceiptPrintedFlg" (296)" needs to be removed from the external metadata column collection.
The "external metadata column "BadRecordFlg" (293)" needs to be removed from the external metadata column collection.
The "external metadata column "TaxExemptUsedFlg" (290)" needs to be removed from the external metadata column collection.
The "external metadata column "DiscountKeyUsedFlg" (287)" needs to be removed from the external metadata column collection.
The "external metadata column "ReenteredTransactionFlg" (284)" needs to be removed from the external metadata column collection.
The "external metadata column "POSOfflineFlg" (281)" needs to be removed from the external metadata column collection.
The "external metadata column "RecalledFlg" (278)" needs to be removed from the external metadata column collection.
The "external metadata column "StoredFlg" (275)" needs to be removed from the external metadata column collection.
The "external metadata column "TrainingModeFlg" (272)" needs to be removed from the external metadata column collection.
The "external metadata column "PumpTestFlg" (269)" needs to be removed from the external metadata column collection.
The "external metadata column "DriveOffFlg" (266)" needs to be removed from the external metadata column collection.
The "external metadata column "CTPFlg" (263)" needs to be removed from the external metadata column collection.
The "external metadata column "VoidTicketFlg" (260)" needs to be removed from the external metadata column collection.
The "external metadata column "NoSaleFlg" (257)" needs to be removed from the external metadata column collection.
The "external metadata column "HomeShoppingFlg" (254)" needs to be removed from the external metadata column collection.
The "external metadata column "TaxAmount" (251)" needs to be removed from the external metadata column collection.
The "external metadata column "TicketAmount" (248)" needs to be removed from the external metadata column collection.
The "external metadata column "NumberOfItems" (245)" needs to be removed from the external metadata column collection.
The "external metadata column "CodeVersion" (242)" needs to be removed from the external metadata column collection.
The "external metadata column "SerialNo" (239)" needs to be removed from the external metadata column collection.
The "external metadata column "TillType" (236)" needs to be removed from the external metadata column collection.
The "external metadata column "CheckoutBank" (233)" needs to be removed from the external metadata column collection.
The "external metadata column "StartTransDateTime" (227)" needs to be removed from the external metadata column collection.
Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "OLE DB Source" (146)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at ArchiveDataFromOneDatabaseToAnother: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ArchiveDataFromOneDatabaseToAnother.dtsx" finished: Failure.

??????????????????????????????

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-02-26 : 12:25:29

Right, error sorted, but how do I dynamically create the table in which to insert (the destination)?

Do I have to somehow copy the table schema first and then do the import?

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources