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
Solved

SSIS Package creation

Posted on 2011-03-16
8
578 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

829 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