Need to increment column value based on Other column In SSIS

sql-server, ssis

Solution

Here is a sample SSIS package created using `SSIS 2008 R2` that demonstrates what you are trying to achieve. The sample package loads the incoming file data into a staging table. Then using the SQL Server `Rank` function in conjunction with `Partition` clause you can load the data as per your requirement. This sample assumes that your database version `SQL Server 2005 or above`. Since you didn't provide an exact XML file format, I have used CSV file format as the input files.

- Let's create two sample CSV files named `Source_001.csv` and `Source_002.csv`. Two files were created just to show the package logic works.

- In the SQL Server database, create the destination table named `dbo.CategoryName`. This is the final table where data will be loaded into. It has the composite key on the columns `Id` and `CatType`.

- In the SQL Server database, create the staging table named `dbo.CategoryName_Staging`. This is where the file data will be loaded into temporarily. This staging table structure mimics the incoming file structure.

In the SQL Server database, create a stored procedure named `dbo.PopulateDestination` using the script provided in the section Stored Procedure Script provided in the bottom of this post. This stored procedure uses `RANK` function in combination with `PARTITION` clause to identify the correct Id that should be assigned to the CatType values.

Create a new SSIS package. Create an OLE DB Connection manager named `SQLServer`. This will point to your SQL Server database. Created a Flat File connection manager named `Source`.

- Configure the flat file connection manager as shown below. I had placed the source csv file in the path `C:\temp\`

- On the Advanced tab of the Flat File Connection Manager Editor, rename the column information. `LegacyId` - integer, `CatType` - string (10), `Name` - string (30) the numbers denote the OutputColumnWidth.

- On the SSIS package's control flow tab, place an `Execute SQL task`, followed by `Data Flow Task` and then followed by another `Execute SQL task`.

- Double-click on the first Execute SQL task and configure it to truncate the staging table.

- Double-click on the Data Flow Task to switch to the data flow tab. Inside the data flow tab, place a Flat File Source manager to read the CSV file and place an OLE DB Destination to write the data into the staging table.

- Configure the flat file source as shown below to read the flat file source using the flat file connection manager.

- Configure the OLE DB destinaton as shown below to accept the incoming data and write into the destination file.

- Go back to Control flow tab, configure the last Execute SQL task to invoke the newly created stored procedure. The package development is now complete.

- Execute the package. Remember, the package is configured to read only the first source file Source_001.csv. After the package execution, query the destination table `CategoryName` to find the following data.

- Now, stop the SSIS package execution, double-click on the Flat File Connection Manager named `Source`. Change the file name path to `Source_002.csv` in order to read the second file.

- Execute the package again. The package is now configured to read the second source file Source_002.csv. This execution will append rows to the already populated desitnation table. After the package execution, query the destination table `CategoryName` to find the following addition data and note that the Id columns is correctly populater.

Hope that helps.

`Stored Procedure Script`:

CREATE PROCEDURE [dbo].[PopulateDestination]
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.CategoryName (Id, CatType, Name, LegacyId)
    SELECT      MAXID.Id + RANK() OVER(PARTITION BY CatType ORDER BY LegacyId) Id
            ,   CS.CatType
            ,   CS.Name
            ,   CS.LegacyId
    FROM        dbo.CategoryName_Staging    CS
    CROSS APPLY (
                    SELECT  COALESCE(MAX(Id), 0) Id
                    FROM    dbo.CategoryName C
                    WHERE   C.CatType = CS.CatType
                ) MAXID
END

Problem

I have a scenario like below. Source Data like below (XML File):: ``` ID CatType Name 11 A Raj 22 A Rai 33 B Raki 44 B Krish 55 A Rem 66 B Ram ``` I have to load above into below formate. ``` ID CatType Name LegacyID 1 A Raj 11 2 A Rai 22 1 B Raki 33 2 B Krish 44 3 A Rem 55 3 B Ram 66 ``` `ID` and `CatType` are composite key in my destination table. I am getting CatType from source. While loading data, I have to increment ID by selecting Max(ID) where CatType= ?(based on CatType) in Destination table How can I load these records in SSIS, could anyone point me in the right direction?

Original source