SSIS Data Transformation

Question

I am trying to transfer data from one db to a new one. i am fetching data from one table say i fetch name of person, then i insert this into say Table Person. this will generate a personID which i want to insert into say Address Table. What should be the approach using SSIS. Any suggestions?

Answer 1

You have a couple of ways to go here.

1. If this is a one time intial load of the table, I'd reccomend using SET IDENTITY_INSERT ON prior to the insert statment. This will allow you to insert the identity yourself, thus negating the need to retrieve the key. You'd have to use a script task or such to create the key sequence.
2. Use a SQL Command task to execute the insert statment and then do a SELECT SCOPE_IDENTITY() to pull the identity of the insert to a parameter you return from the SQL Command task. This is risky as you can not be guranteed @@identity is from your insert. This is only a real risk if there are multiple inserts going on from other users.

So if you decide to go for #2, here is how you would do it.

Create Two Tables to represent your Old Sytem and New System:

CREATE TABLE [dbo].[Person](
    [ID] [int] IDENTITY(100,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[Person_OldSystem](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NULL,
    [LastName] [varchar](50) NULL
) ON [PRIMARY];

Fill The person Table with two entries:

alt text http://www.freeimagehosting.net/uploads/ff56e32bea.gif

Create a stored procedure to do the inserts and return the new id:

ALTER PROCEDURE [dbo].[sp_InsertPerson] 
    @Fname varchar(50),
    @Lname  varchar(50),
    @id integer OUTPUT
AS
BEGIN

    INSERT INTO QandA..Person (FirstName, LastName) VALUES (@Fname, @Lname);
    SELECT @id = SCOPE_IDENTITY();

END

Next, we'll set up the SSIS package. In this sample package, add a single Data Flow task. In the data Flow Task add the following tasks and wire them up as shown.

alt text http://www.freeimagehosting.net/uploads/5348332a9e.gif

Note the data viewers are there to show you the results as we progress.

Set up the OLE DB Source Task to pull all of the columns from the Person_OldSystem table.

Set up the Derived Column Task to add a column called 'NewID'

alt text http://www.freeimagehosting.net/uploads/a5c6c9e7c6.gif

Set up the OLE DB Command Task with the following SQL.

EXEC sp_InsertPerson ?, ?, ? OUTPUT

In the OLE DB Command Task's Advanced Properties, set up the following column mappings:

alt text http://www.freeimagehosting.net/uploads/2224622431.gif

So what we've done with the data flow is to extract the list of people from the old system. We then add a new column, called NewID, to hold the identity of the row when it is inserted into the new table.

alt text http://www.freeimagehosting.net/uploads/8162127377.gif

The OLE DB Command calls our stored procedure which does the insert and reutrn the id of the new row in the last parameter. This returned id is then mapped into the column in the data flow we preared for it.

alt text http://www.freeimagehosting.net/uploads/97dbfba277.gif

The conditional split is there to give the data flow somewhere to go.

Answer 2

It all depends on your specific situation. Here are a couple of possibilities:

1. Do you have the business key (for instance the name of the person - anything that uniquely identifies a Person record) of the person available when loading the Address table? If yes, use that to perform a lookup on the Person table.

2. Or are you more looking for a way to create new Address records for the newly-created Persons? In that case, the ideal situation would be that you have a Created datetime field in your Person table which would get filled at the moment that the Persons are inserted. You can then use that timestamp to retrieve all Person IDs, for instance all Person records with Created > the largest Created timestamp of the Address table.

Again, it all depends so the above may need some adaptation to your specific situation. I do hope it gets you started though.

Answer 3

I would suggest having two separate dataflows-first one loads your person table from your ? source table and the second one loads your address table with the person id.

Answer 4

Create another control flow task and in the data flow section, fetch the personid from the source table which you said as Person table and insert into the destination table which is the Address table