CODEWITHSUNDEEP
ssis
David
David

Reputation: 1041

How to separate column using an SSIS expression?

I have a CSV file and one of the columns is concatenated like: ".col1:.col2"

I am using SSIS to load CSV file into a SQL table.

How can I separate the column?

Thanks.

Upvotes: 1

Views: 661

Answers (1)

Santiago Cepas
Santiago Cepas

Reputation: 4094

Here is how you could do it:

  1. Add a data flow task
  2. As data source set up a flat file connection, pointing to you CSV file.
  3. Add a derive column transform, with two columns as follows:

Col1, with the expression: SUBSTRING(COLNAME, 1, FINDSTRING(COLNAME, ":", 1) - 1)

Col2, with the expression: SUBSTRING(COLNAME, FINDSTRING(COLNAME, ":", 1) + 1, LEN(COLNAME) - FINDSTRING(COLNAME, ":", 1))

Finally, load the relevant fields of the dataflow into you Sql destination.

I haven't tested the expressions myself, so they might have some one-off errors.

Upvotes: 2

Related Questions