I want to dynamically read columns and concatenate values by using in SSIS

Question

I want to read columns dynamically and concatenate values by using in SSIS. And it should generate a string of concatenate value which is delaminated by '|'. Nunber of columns will be not constant. Here is an example of what I am trying to do.

Input

Col1   Col2   Col3
123 ABC DEF

Output

Data
123|ABC|DEF

Could you please provide C# script for the same or any other solution.

Thanks, Nitin

Why don't you try writing the script yourself? Which part of it are you stuck on, or unsure how to do? — Tab Alleman, Dec 14 '15 at 14:20

Senthil_Arun · Answer 1 · 2015-12-14T08:44:59.723

here is the script to retrieve the SQL Statement, note you need to convert the non-character/string columns to string type, then only the concatenation will work

DECLARE @tblName NVARCHAR(250), @SQLstmt NVARCHAR(MAX)
SET @SQLstmt = ''
SET @tblName = <Replace with your table name>
SELECT @SQLstmt = @SQLstmt + '|' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tblName
SET @SQLstmt = SUBSTRING(@SQLstmt,2, LEN(@SQLstmt)-1)

PRINT @SQLstmt

I want to dynamically read columns and concatenate values by using in SSIS

1 Answers1