0

I have an SSIS package that takes data from a csv file goes through a data conversion to convert data type then imports into a database.

I wanted to remove part of a string from a column before importing it into the database so I included a derived column transformation.

I put the derived column task before the data conversion task. I set the Derived column to replace the column I want to change then put my expression in to replace the part of string.

It then goes to the data conversion and then to the import to database task. when I query the database it has put all the data from column 1 field into column 2 field it has not put the derived data in the column 2 field as it should because the fields are all mapped correctly.

has anyone else come across this?

sql2015
  • 591
  • 3
  • 13
  • 34
  • some screen shots from your package showing the data flow task and data mapping would help to understand the issue – Ajan Balakumaran Oct 23 '19 at 08:08
  • Are you 100 percent certain columns are mapped correctly, ex: `alias of derived column -->column2` – Ven Oct 23 '19 at 08:10
  • 1
    @Ven yes I believe so. I have my [Derived Column] Set to Replace 'Column2' and my [Derived column Name] as 'Column2'. then in my data conversion task I have [input Column] as 'Column2' [output Alias] as 'Copy of Column2' then in my OLE DB Destination mappings I have [input column] 'Copy of Column2' [Destination Column] 'Column2' – sql2015 Oct 23 '19 at 08:15
  • I could only think of comma values in column 1. For a CSV , i will always prefer to capture raw data into a staging table, and then insert into the actual table. You could actually think of all possibilities with CSV file coz we dont live in a ideal world – Ven Oct 23 '19 at 08:50

1 Answers1

0

I had set up everything correctly with my mappings my issue was in my REPLACE Expression in the derived column transformation task I was referencing column1 not column 2. have now resolved this.

sql2015
  • 591
  • 3
  • 13
  • 34