Solved

SSIS Package creation

Posted on 2011-03-16
8
577 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:
Jason Yousef, MS 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 

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:Jason Yousef, MS
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

803 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