Transforming SSIS Data

You have several ways to go here.

• If it is a one-time load of the table, then I would recommend using SET IDENTITY_INSERT ON before inserting. This will allow you to insert credentials yourself, thereby negating the need to retrieve the key. You will need to use a script task or such to create the key sequence.
• Use the SQL Command task to perform the stat insert, and then execute SELECT SCOPE_IDENTITY () to output the insert ID to the parameter returned from the SQL Command task. This is risky as you cannot be guranteed @@. This is only a real risk if there are multiple investments with other users.

So, if you decide to go to # 2, here's how you do it.

Create two tables to represent your old system and your 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 in the "Person" table with two entries:

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

Create a stored procedure for insertion and return a new identifier:

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

      

        
        
        
      

    

Then we will configure the SSIS package. In this sample package, add one data flow task. In the Data Flow Task, add the following tasks and connect them as shown.

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

Note that data watchers should show results as they progress.

Set up an original OLE DB task to pull all columns from the Person_OldSystem table.

Configure Derived Column task to add a column named 'NewID'

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

Issue an OLE DB command line command with the following SQL.

EXEC sp_InsertPerson ?, ?, ? OUTPUT

      

        
        
        
      

    

In the advanced OLE DB command line properties, set the following column mappings:

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

So, we did it with data flow to fetch a list of people from the old system. We then add a new column called NewID to preserve the identity of the row when inserted into a new table.

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

The OLE DB command calls our stored procedure, which inserts and re-enters the newline id in the last parameter. This returned identifier is then mapped to a column in the data stream, which we are ready for.

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

Conditional splitting is to give the data stream somewhere to go.