Solved

SSIS Package creation

Posted on 2011-03-16
8
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 40

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Sum totals 2 31
tempdb log keep growing 7 45
TSQL Challenge... 7 44
SQL Server 2008 R2, need a pivot/cross tab query... 4 54
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 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