Solved

SSIS Package creation

Posted on 2011-03-16
8
575 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:sqlcurious
  • 5
  • 2
8 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 35150530
Not very clear what you want to achieve and why so complicated? Why not export data from
One SQL server directly to another SQL Server by maping table(s).column(s) betwwen the two server databases? Only reason I could think of is if they are on separate domains and you need to copy the data over from one box to another.
Anyway, you could export data from diferent tables in the same XLS doc but under diferent sheets and you will be using one SSIS package for that where obviously you need to do the mapping between table/colums and excel sheets.
The in my opinion you will have another SSIS package to import data into your "one database" whatever that is and obviously you need to map the excel sheets/columns to your destination SQL Table(s).Columns
This is not very pretty that's why I asked why you want to do it this way when you could have a single SSIS package with multiple connections to multiple servers.database.tables then map/transform data by dts tasks into your destination database.table(s).column(s)

SQLServer1.TableA.columnX, SQLServer1.TableA.columnY, SQLServer1.TableA.columnZ
0
 

Author Comment

by:sqlcurious
ID: 35150775
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
0
 
LVL 21

Accepted Solution

by:
huslayer earned 250 total points
ID: 35156256
Hello,
Yes you can use 1 SSIS package and 1 Excel file.

SSIS "excel destination" uses an already existing file, which you can redirct each database to a separate excel sheet.  as "Lcohan" said too.

see atatched screen shot, this is only to show you how all sources into one excel file.

the question now, is all the tables have the same structure inside their own DB ?

you can loop over each database to get the table name into a variable, use the variable as source to get the data from and export it into the excel file.

hope that helps.
Jason excel
0
 

Author Comment

by:sqlcurious
ID: 35156699
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
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sqlcurious
ID: 35156738
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
0
 

Author Closing Comment

by:sqlcurious
ID: 35167229
since both tried to give the solution i have awarded equal points
0
 
LVL 21

Expert Comment

by:huslayer
ID: 35176625
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
0
 

Author Comment

by:sqlcurious
ID: 35183353
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
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now