We have an Excel file like this (date column in DD-MM-YYYY):
We have a Date Dimension table like this (date column in YYYY-MM-DD):
Using SSIS 2012, we want to convert the date column in Excel from DD-MM-YYYY to YYYY-MM-DD so we can lookup the Date_Key value in the Date Dimension.
We want to output it in text file (error rows go to Error File):
We need to build an SSIS 2012 package like this:
The package reads the Excel file. It convert the date column in the Excel file to YYYY-MM-DD using Derived Column. It reads the Date Dimension table using Lookup transform, then lookup the Date_Key column. It then output the file to an Output file, and the error rows (failed lookup) to the Error File.
Before We Begin
SSIS Excel adapter can only run in 32 bit mode. So right click your project name, properties, and set Run64BitRunTime to False:
Step by Step
Step 1. Read the Excel File
On SSIS Toolbox, drag Data Flow Task to Control Flow canvas:
In SSIS Toolbox, double click Source Assistant, choose Excel:
Click New, choose the Excel file to import. Data access mode = Table or View, Name of Excel sheet = Sheet1$. Click Preview:
Click Column on the left pane to verify that the column names are OK, then click OK to close the Excel Source Editor.
Step 2. Convert to YYYY-MM-DD Using Derived Column
From the SSIS Toolkit on the left hand side, drag Derived Column onto the Data Flow canvas. Configure it as follows:
Expression: (DT_WSTR,4)YEAR(Date) + “-” + RIGHT(“0” + (DT_WSTR,2)MONTH(Date),2) + “-” + RIGHT(“0” + (DT_WSTR,2)DAY(Date),2)
Date being the column from the Excel file.
At this step I would recommend setting up a Flat File destination and output the Derived Column result into it. This is to check that the date column in Excel file has been converted correctly.
If not, at least setup a Data Viewer so you can see what’s flowing out of the Derived Column.
Step 3. Read the Date Dimension using Lookup and get the Date Key
Drag Lookup transform from the Toolkit to the Data Flow canvas. Setup a connection to your SQL Server. Go to Connection pane, and configure as follows:
In the above SQL query box, you need to convert from varchar to nvarchar, otherwise it won’t match the output of the Derived Column which is WSTR (wide string / unicode). STR in SSIS data type = varchar, WSTR = nvarchar.
Click on Preview to see the data from the Date Dim:
Click on Columns pane on the left and configure as follows:
On the General tab, configure Redirect rows to Error output for No Matching Entries:
4. Configure Output File and Error File
Drag Flat File Destination from the Toolkit panel onto the canvas and configure as follows:
On the Output Flat File Destination Editor, configure the Mapping pane as follows:
Do the same with the Error Flat File Destination Editor:
That’s it you can now run the package: