This article will show how to add the variable value in the data source output like xml/josn/csv/any other source and manipulate data source column value.
Sometimes you want to add variable values in the output or you want to manipulate column values e.g. you want to replace some special character or the text if columns hold such character or text.
So using SSIS Native Derived Column Transformation we can easily achieve this. in this article we will add a new column named as PurchaseDate and pass the variable value in it and will replace & with & if the column holds that character.
This is our Actual Data Source Output.
Now using Derived Column Transformation below the Data Source we will add the new column named it as PurchaseDate and will manipulate Title column data, we will replace & with &
- Below the Data Source add the Derived Column
- And configure Derived Column like this.
- Select the <add as new column> in the Derived Column, name it as PurchaseDate, and drag and drop the desired variable in the Expression and it will add a new column in the OutPut.
- Now for the search and replace in the title column, select Replace 'title' in the Derived column and pass below Replace expression.
REPLACE([title] , "&", @[User::vReplaceAmp])
That's it and you will get the desired output