You may need to convert one or more of your date fields into an integer field. Maybe you will be using this field as a Key for your Data Warehouse DimDate field where the format would be yyyymmdd with no dashes. I will be using "Derived Columns" in SSIS to accomplish this. Derived Columns is a great tool to add a new column or add data to existing columns during your ETL Process. Maybe you want to create a column that concatenates the FirstName field and LastName field. Derived Columns is a great way achieve this.
In this blog we will concentrate on adding functionality to my previous blog’s ETL Process, http://sqlblog.net/2014/05/01/insert-and-update-records-with-a-ssis-etl-package/.
We first need to add a column to the dbo.Emp table under the Running Totals database. I added a field called “StartDateID” with an “integer” data type. In this post we will be converting the “StartDate” field into the new field “StartDateID” field. You can see that the output for this field is all NULL values.
Now, let’s modify the Stored Procedure we use in the "OLE DB Command" of the ETL Package.
The image below shows where we left off in the ETL Process.
I will be adding the Derived Column to the canvas between the Merge Join and the Conditional Split. Delete the line arrow between the two, and drag Derived Column between them and connect the arrows. It should look like the following image.
Double Click “Derived Column” and enter the name of the new field “StartDateID” in the Derived Column Name.
Copy and paste the following into the Expression section. If you read through this expression it is pretty much self-explanatory, but is based off of my c.StartDate field.
The next thing we need to do is modify the OLE DB Command to include the new field from the Stored Procedure that we modified earlier. Double-click “OLE DB Command.” Select the Component Properties tab. Add an extra 'comma' and 'question mark' to accommodate the new field. Next select the “Column Mapping” tab and connect the StartDateID with the @StartDateID.
You will need to add the new field in "Sort DB" by checking the box StartDateID
In the OLE DB Destination, connect StartDateID Input to StartDateID Destination.
Run the Package and check the table again, and you will see the new StartDateID field has been populated from the StartDate field.