August 29, 2012 at 8:53 pm
Hello,
I have a "staged" table that holds many columns, one of them being a date column. The Date column is "SHIPPED_DATE" and the column type is a "varchar(10)".
The data inside looks like this:
12062006
12152006
12212006
03042011
02242011
02242011
02242011
I want to store this data into a SQL database that is of "date" type. What do I need to do in my SSIS to make this happen?
Storing it as "date" type would make the final output "MM-DD-YYYY" instead of "MMDDYYYY" correct? I am looking to using SSIS convert to Date DataType from VarChar and store it as "MM-DD-YYYY" instead of "MMDDYYYY" in the SQL date column.
Anyone able to show me how I can do this?
I try to do a Derrived Column Type Cast of DT_DATE but I get errors.
August 29, 2012 at 8:56 pm
My error is:
"The column status returned was: "The value could not be converted because of a potential loss of data."
This is when I even try doing a extract from staged column varchar(10) directly to date column without transformation in between.
What do I need to do?
Thanks.
August 31, 2012 at 1:04 am
Try doing a derived column transformation to parse out your original varchar(10) string to your mm-dd-yyyy string that you want, then use a data conversion transform to change the mm-dd-yyyy string to datatype datetime.
HTH,
Rob
September 4, 2012 at 8:31 am
You could also convert (or CAST) the column in the T-SQL from your Source Component, then send through a Data Conversion component before sending to Destination component.
SELECT <columns>, CAST( txtDate AS DateTime)
FROM <stage table>
thomas
Thomas LeBlanc, MVP Data Platform Consultant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply