Date Format Conversions in SSIS

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.

Greg.

Date conversion

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

Phil,

That cannot be explicitly casted as a DT_DBTIMESTAMP because of the "-" between days and hours and the "."'s in the time. The following expression in a derived column expression should work:

(DT_DBTIMESTAMP)(SUBSTRING(<column_name>, 1,10) + " " + SUBSTRING(<column_name>, 12,2) + ":" + SUBSTRING(<column_name>, 15,2) + ":" + SUBSTRING(<column_name>, 18,2))

OK, I've done this from memory so it might not work exactly correctly but hopefully you get the idea and you can modify it appropriately.

-Jamie

|||Thanks Jamie.|||

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

|||

ckeaton wrote:

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1|||Thank you!

Date conversion

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

Phil,

That cannot be explicitly casted as a DT_DBTIMESTAMP because of the "-" between days and hours and the "."'s in the time. The following expression in a derived column expression should work:

(DT_DBTIMESTAMP)(SUBSTRING(<column_name>, 1,10) + " " + SUBSTRING(<column_name>, 12,2) + ":" + SUBSTRING(<column_name>, 15,2) + ":" + SUBSTRING(<column_name>, 18,2))

OK, I've done this from memory so it might not work exactly correctly but hopefully you get the idea and you can modify it appropriately.

-Jamie

|||Thanks Jamie.|||

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

|||

ckeaton wrote:

I have a similar problem. My input date is YYYYMMDD. I have been assuming that this would implicitly convert when I use the data conversion object in SSID to make it a DT_TIMESTAMP or DT_DATE, but I get the error that the original poster experiences.

I've checked the data source and there aren't any NULL values or weird dates. Any ideas?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884269&SiteID=1|||Thank you!