March 8, 2009 at 12:53 am
hi,
I am new to sql server, i need some help in designing a package....
i have some data in old version of sql server and i also have new database in new version.I have to move some fields in the old version to the some tables in the new version............
In the new database, i have 12 tables which have FK relations between each of them.Now, how should i start my package??
how can i relate the tables in the packages.........please help me.........
Thanks,
priya
March 8, 2009 at 4:44 am
Well, you don't actually relate tables in SSIS, instead you create multiple data flows that transfer data from source to target tables with proper workflow order.
You will need 2 connections (one for each source & target servers) & 12 data flow tasks (for each table). And in the data flow task, you will have Lookup component to fetch the parent information from either source/target.
--Ramesh
March 9, 2009 at 7:43 am
Pretty much what Ramesh said.
Open up BIDs then File and new and new Integration services package. At the bottom in the centre there is the connection manager tab. Here you just right click and add a connection. You will need to create a connection to the old database and another connection to the new database. Then the centre of the screen shows the controil flow. Just add a data flow task to the control flow. Then click on the data flow tab (above the working area) and add oledb source. Edit the oledb source to use the first connection manager and you could write the sql to get the exact columns or select the table if you wanted it all. Then add an oledb destination. Repeat the same process for destination providing all the details of where the data is going. The Drag the green line from source to destination. On the destination edit and look at mappings and align the source columns to the destination.
Or if its a one off:
Right click on the destination database in SQL Server Management Studio. Then 'Tasks', then 'Import Data'. This brings up a waizard. On the first screen select the instance and database, then you get a destination screen and select the database you want to use as a destination. The next screen allows you to select the table you want to mpove from source to destination. This is very easy for a one off. Additionally doing this there is a tick box that allows the wizard to create a package to do this and store it for you.
Hope this helped too.
Ells
:hehe:
April 8, 2009 at 9:20 am
I am new at this as well. I think I am doing pretty much the same thing.
I have 3 pieces of data coming from 3 different databases so as I understand it I need 3 different data flows as I am getting 3 different pieces of data.
For each data flow I'm using an OLEDB source which is connected to a derived column which connects to an OLEDB destination. The destination is the same for all 3 pieces of data.
The only problem is that this is creating 3 new rows and I only want one row with each piece of data in a different column.
Any help on this would be greatly appreciated.
April 9, 2009 at 12:53 am
Jerry I think I need a clearer explanation of what you are doing
I am guessing you are taking in three data feeds that want to end up as three columns in one table. As an example somrehing like customer phone number as source data 1, customer address as 2 and the third customer name. Then you want to put this into custimer table as three columns but only one row per customer. Is that the sort of thing??
You could uses data flow task 1 to import into a staging table for feed 1
You could uses data flow task 2 to import into a staging table for feed 2
You could uses data flow task 3 to import into a staging table for feed 3
Then in the control flow after the three data flow task drop down an 'Execute SQL task' that contains the SQL to insert / update / delete.
If you use proper temporary tables then you will need to go to the three\four connection (3 sources and one destination) in connection manager and on the properties set 'retain same connection' property to true.
If you are doing something else then ther needs to be a more detailed description of what you are trying to do.
Regards,
Mark
, I am new at this as well. I think I am doing pretty much the same thing.
I have 3 pieces of data coming from 3 different databases so as I understand it I need 3 different data flows as I am getting 3 different pieces of data.
For each data flow I'm using an OLEDB source which is connected to a derived column which connects to an OLEDB destination. The destination is the same for all 3 pieces of data.
The only problem is that this is creating 3 new rows and I only want one row with each piece of data in a different column.
Any help on this would be greatly appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply