SSIS Data Transformation

Question

Hi, I am new to SSIS, so please bare with me.

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

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 2

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:

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.

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'

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:

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.

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.

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

Answer 3

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