Date conversion in SSIS ETL

I have date in Flat file and it is in the string format,but now i want to convert it in to normal date format.I have tried doing this by SSIS but it is not working.Are you trying to use a data conversion transform? What error are you getting?|||yes in Data flow transformation in intregartion services.I have numerously but i have not succeded|||and dat is with Double quotes.|||Please post an example of the date data and we'll try to help you from there.|||

This is example of flat file in .csv

890512 means 05/12/1989 .

When i uploda using SSIS 890512 becomes "890512" and data type is string.

I want to convert "890512" to date format but i m getting error.

|||You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?|||What is to be given in SSIS text qualifier.I can not get it.|||

Nirad Pachchigar wrote:

What is to be given in SSIS text qualifier.I can not get it.

"|||

now uploading is fine without "",but still i can not convert date into proper format.i got error on say after 6514 column.

Error: 0xC02020C5 at Data Flow Task, Data Conversion [1]: Data conversion failed while converting column "DATE" (60) to column "Copy of DATE" (249). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Data Conversion [1]: The "output column "Copy of DATE" (249)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of DATE" (249)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

|||

Phil Brammer wrote:

You shouldn't have the double quotes in your data when reading a .CSV file into SSIS. If you do, then perhaps there's an error in how you set up your flat file import. Make sure the text qualifier is set to double quotes.

But before we give an example of how to work with your non-year 2000 compliant date format, what are the rules for prefixing the year with "19" or "20"?

You can't convert the date as it is, you'll need to format your string. To do that, you should be using a 4-digit year as well. Answering my previous question will help ensure you do it correctly.

However, this is how you get started:

(DT_DBDATE)(substring([datecolumn],3,2) + "/" + substring([datecolumn],5,2) + "/" + substring([datecolumn],1,2))|||

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

|||

jwelch wrote:

See this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=506809&SiteID=1. It shows an example of using SUBSTRING to grab the components of the date from a string and convert to a date. As Phil mentioned above, you'll need to add some logic to handle what century the date belongs in.

(DT_DATE)(SUBSTRING(YourDate,3,2) + "/" + SUBSTRING(YourDate,5,2) + "/" + (SUBSTRING(YourDate,1,2) < 40 : ("20" + SUBSTRING(YourDate,1,2)), ("19" + SUBSTRING(YourDate,1,2)))

Not sure if got the syntax exactly right (don't have the IDE available right now), but it should be close.

Real close, John. And this assumes that when the year is 0-39, you prefix it with "20" or else you prefix with "19".

Here's the corrected syntax:
(DT_DBDATE)((SUBSTRING(YourDate,3,2) + "/" +

SUBSTRING(YourDate,5,2) + "/" + ((SUBSTRING(YourDate,1,2) < 40 ?

("20" + SUBSTRING(YourDate,1,2)) : ("19" + SUBSTRING(YourDate,1,2)))))

Here's a question for the OP though. Since this is a numeric number stored as an integer and without the century indicator for the year, what happens when we are talking about March 25, 2007? Is the date listed as 70325, or 070325? This will make a difference in how you convert the date.