asked on

SSIS Package creation

I need to transfer 3 database tables into a single database.For this i created a pacakge to transfer tables from one database to an
excel sheet using SSIS export and import wizard.The table structures for the source database and the destination database are different.
db1 has 35 tables db 2 has 10 tables and db 3 has 5 tables.

My Question is do i have to map the table name and column name of the source table and the destination table at the time of package creation by edit mapping
or i can map after table has been transfered to the excel sheet?.And if i do it after transferring to the excel sheet,wont there be the issue
if i run the package again?

Another question is do i need to create 3 packages or i can do it by using only one package?I want to do it using one package only, if so please suggest me the steps
Can i transfer all the tables from 3 source db to single excel sheet or need to create separate excel sheet?

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

yes i understood ur view

Actually i need to do by transferring to excel file and then to database

Is single package is possible to transfer the tables from three different database to the single excel file or i need three excel files and 3 packages for three different databases.If it can be done by single package please tell me the solution

I did not find how one ssis package can be used to transfer tables and data from 3 different databases to excel files

ASKER CERTIFIED SOLUTION

Jason Yousef

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sqlcurious

ASKER

The table structures are different in the source database and in the destination database and each source db has many tables how to work on that ?please suggest

sqlcurious

ASKER

As u suggest looping over each database to get the table name into a variable and use the variable as source to get the data from multiple tables?Can u tell me the steps?

I am new to SSIS

sqlcurious

ASKER

since both tried to give the solution i have awarded equal points

Jason Yousef

Thanks for the points and the grade and I'm sorry I was away and just saw your reply, I'll give you more details later about the Loop over the database.

you can do it with one SQL query to list all the tables and use BCP to export to CSV or OPENROWSET to export to excel.

will keep you updated, I'll blog about it at http://asqlb.blogspot.com/

thanks
Jason

sqlcurious

ASKER

appreciated for your help.Can u do it as soon as posiible because i am new to ssis and have been stucked for a long time in looping over database