Importing one file into two database tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Importing one file into two database tables

Importing one file into two database tables

Rate Topic

Display Mode

Topic Options

Author

Message

dbman

Posted Wednesday, January 09, 2013 8:40 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:42 AM
Points: 71, Visits: 1,067

I have one flat file that i import into a table using an SSIS package. In BIDS i have a data flow task with Flat File pointing to OLE DB Destination.

I have another table in Database that i need to import the same file. Is there a way to do this using the same SSIS package, rather than creating another package?

Thanks

Post #1404835

anthony.green

Posted Wednesday, January 09, 2013 8:46 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Within the same DFT, just drag in another flat file source and another destination and mapp the two.

So 2 Flat File Sources, pointing to the same Flat File Connection Manager, 1 OLE DB Destination to DB1, 1 OLE DB Destination to DB2.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1404838

Phil Parkin

Posted Wednesday, January 09, 2013 8:47 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

Yes.

In the data flow, after the data source, add a MULTICAST.

Connect one MULTICAST output to the existing table and another to the new table.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1404840

anthony.green

Posted Wednesday, January 09, 2013 8:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Thats one to investigate, never used multicast.

Post #1404842

dbman

Posted Thursday, January 10, 2013 3:35 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:42 AM
Points: 71, Visits: 1,067

Hi

I tried both methods but 0 rows were written to the OLE DB Destination 1 (new table).
Is there something I am missing?

Post #1405314

Phil Parkin

Posted Thursday, January 10, 2013 3:51 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

ziako (1/10/2013)

Hi

I tried both methods but 0 rows were written to the OLE DB Destination 1 (new table).
Is there something I am missing?

Are you saying that it previously wrote many rows, you added a multicast and now it writes zero?

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1405323

dbman

Posted Thursday, January 10, 2013 4:22 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:42 AM
Points: 71, Visits: 1,067

The package has pushed files to the first table but has written 0 files to the second table.

Post #1405336

Phil Parkin

Posted Thursday, January 10, 2013 4:24 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

Files?

You mentioned only one in your first post.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1405337

Bruce W Cassidy

Posted Friday, January 11, 2013 12:25 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945, Visits: 998

One possible reason why there are no rows going into the second table is that you have the multicast in the wrong place. The multicast needs to happen before the write to the first table. You can think of it as creating an identical copy of every row that comes into the multicast. Then the two results of the multicast go to the first and the second table (one each).

Another possible reason why there are no rows being inserted into the second table is that there is something wrong with the insert (permissions or missing fields or incorrect fields or the like). However, you should see errors if that were the case.

Post #1406227

« Prev Topic | Next Topic »

Permissions