?
Solved

SSIS Package creation

Posted on 2011-03-16
8
Medium Priority
?
584 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 1000 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 1000 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

649 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