SQL Server Business Intelligence

SQL SERVER 2005 & 2008 IS THE MAIN THEME OF THIS BLOG

Tuesday, March 14, 2006

Date Source File in US Format. Database Locale is Set to British. Need to Get Surrogate Key?

If you are receiving the date source file with US format (MM/DD/YYYY). It may sound odd, but in the project I am currently working on I had to overcome this issue. The date dimension is stored in the table using DD/MM/YYYY format. You wish to get Surrogate Key using Lookup feature within SSIS.

Source File As shown below:
Col1 Col2 Col3 Col4
1 USD 01/01/2006 0:00 0.99980004
1 USD 01/02/2006 0:00 1.000900811
1 USD 01/21/2006 0:00 0.99960016

I would suggest you define the column3 as STRING in the flat file connection manager. Once you have defined the data type you can use Derived Column component to convert the string into the correct format as shown below :
(DT_DBTIMESTAMP)(SUBSTRING([Column 2],4,2) + "/" + SUBSTRING([Column 2],1,2) + "/" + SUBSTRING([Column 2],7,4))

You can download the sample package from here.

Try to avoid using DATEPART. If you wish to use DATEPART then you would have to set the source field (in this example Column 2 need to be defined as DATETIMESTAMP instead of STRING). The statement below will give you similar result, but there is GOTCHA.

(DT_STR,2,1252)DATEPART("dd",[Column 2]) + "/" + (DT_STR,2,1252)DATEPART("mm",[Column 2]) + "/" + (DT_STR,5,1252)DATEPART("yyyy",[Column 2])

The above script will produce the following result set.

1 USD 01/01/2006 0:00 0.99980004 01/01/2006 - This is right
1 USD 01/02/2006 0:00 1.000900811 01/02/2006 - This is NOT right
1 USD 01/21/2006 0:00 0.99960016 21/01/2006 - This is right

First of January worked fine. Second of January didn't work fine. Twenty first worked fine. Therefore be aware when you use it.

If you receive date in the following format 01-JAN-2006 then you can use the following syntax to convert it to date. You would need to define the source as String.
(DT_DBTIMESTAMP)(SUBSTRING([Column 2],1,2) + "/" + SUBSTRING([Column 2],4,3) + "/" + SUBSTRING([Column 2],8,2))

Thanks
Sutha

posted by Sutha Thiru @ 2:22 AM  

4 Comments:

  • At 11:35 PM, Blogger Sutha Thiru said…

    Hiya

    If I understand the question correctly you would like to specify the default value in the format file. I would say the deafult values should be specified against the columns in a table. If you have done that then you leave that column as null and the data will be loaded with default values.

    http://msdn2.microsoft.com/en-us/library/ms187887(SQL.90).aspx

    Have a look at the above article.

    If it doesnt answer the question please let me know and I will investigate further for you.

    Thanks
    Sutha

     

  • At 7:51 AM, Blogger Sutha Thiru said…

    I think because you are reading it in as DT_STR, try this code :

    (DT_DBTIMESTAMP)(TRIM(AGFEAL) == "") ? NULL: (DT_DBTIMESTAMP)(SUBSTRING(AGFEAL,7,2) + "/" + SUBSTRING(AGFEAL,5,2) + "/" + SUBSTRING(AGFEAL,1,4))

    Thanks
    Sutha

     

  • At 8:19 AM, Blogger Sutha Thiru said…

    Please Ignore the above message as it won't work.

    Try to follow the steps...

    1. Read the source file. Field in question should be DT_STR when we read the data.
    2. Derived column (add new col), which will do the following:
    (TRIM(Col2) == "") ? NULL(DT_WSTR,0) : (SUBSTRING(Col2,7,2) + "/" + SUBSTRING(Col2,5,2) + "/" + SUBSTRING(Col2,1,4))
    3. Derived Column, which will do the following :
    (DT_DBTIMESTAMP)(NewCol2)

    I think this should resolve the issue, but I didn't spend much time thinking about it.

    Let me know how you get along

    Thanks
    Sutha

     

  • At 2:28 PM, Blogger jerryo said…

    Sutha, thanks so much. It worked great for me (wish I found it 6 hours earlier!)

     

Post a Comment

<< Home