Data Warehousing and Business Intelligence

28 December 2012

SSIS 2012: Converting Date from Excel for Lookup

Filed under: SSIS — Vincent Rainardi @ 12:12 am

We have an Excel file like this (date column in DD-MM-YYYY):

Excel File

We have a Date Dimension table like this (date column in YYYY-MM-DD):

Date Dimension

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):

Output FileError File

Overall

We need to build an SSIS 2012 package like this:

SSIS 2012 Package

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:

Run64bit

Step by Step

Step 1. Read the Excel File

On SSIS Toolbox, drag Data Flow Task to Control Flow canvas:

Data Flow Task

In SSIS Toolbox, double click Source Assistant, choose Excel:

Source Assistant

Click New, choose the Excel file to import. Data access mode = Table or View, Name of Excel sheet = Sheet1$. Click Preview:

Preview Excel File

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:

Derived Column

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:

Lookup Transform

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:

Lookup - Preview

Click on Columns pane on the left and configure as follows:

Lookup - Columns

On the General tab, configure Redirect rows to Error output for No Matching Entries:

Lookup - General

4. Configure Output File and Error File

Drag Flat File Destination from the Toolkit panel onto the canvas and configure as follows:

Output File - General

Output File - Columns

Output File - Advanced

On the Output Flat File Destination Editor, configure the Mapping pane as follows:

Output Flat File Destination Mapping

Do the same with the Error Flat File Destination Editor:

Error Flat File Destination Mapping

That’s it you can now run the package:

Overall

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: