Generate auto generated string while importing data using SSIS

Question

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:

Destination:

Thanks in advance.

Answer 1

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.

Answer 2

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.