Share via

Generate auto generated string while importing data using SSIS

Bala Narasimha Challa 466 Reputation points
Nov 4, 2024, 4:18 PM

Hi Team,

Could you please help me on bellow issue.

Am pushing data from snowflake to SQL server using SSIS and while loading need to generate incremental string in destination.

For example:

Source:

User's image

Destination:

User's image

Thanks in advance.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,601 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Michael Taylor 55,216 Reputation points
    Nov 4, 2024, 4:46 PM

    It isn't clear how you're inserting your data into the target DB but I assume you're using a copy destination task or equivalent. There are a couple of ways to do this. I think ideally you should just use a Copy Column Transformation and use an expression to populate it as part of the copy. Use the transform to take the ID column and generate the UniqueID column from it. I believe you can do this with a Derived Column transform. Perhaps an expression like 'A' + RIGHT(REPLICATE('0', 10) + ID, 10) There might be an easier way but the formula prepends 10 0s to the ID and then truncates the value to the rightmost 10 characters. If your IDs are going to be larger than adjust the lengths to account for that.

    Another approach, if the transform won't work, is to import the data and then add a calculated column that does basically the same thing after the fact. Persist the column and save it. This will fill the data. Then change the column from a calculated column to a regular string column. This would be a last resort if you couldn't get the transform working properly.

    0 comments No comments

  2. Olaf Helper 45,206 Reputation points
    Nov 4, 2024, 5:04 PM

    You can use a Derived Column Transformation to "calculate" then UniqueID.

    I personally wouldn't persist such a redundant information. Create a view to calc the ID on the fly or a calculated column in the table.

    0 comments No comments

  3. ZoeHui-MSFT 38,066 Reputation points
    Nov 5, 2024, 1:29 AM

    Hi @Bala Narasimha Challa,

    You may add Derived Column between the Source and Destination components.

    And add a new column in Derived Column with expression "A00"+[id], you could see below.

    User's image

    User's image Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.