July 21, 2014 at 7:30 am
Hello,
in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?
July 21, 2014 at 9:00 am
arkiboys (7/21/2014)
Hello,in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?
Lookup the CONVERT function in Books On Line. That'll do it for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 21, 2014 at 9:03 am
Jeff Moden (7/21/2014)
arkiboys (7/21/2014)
Hello,in ssis, I use a oledb source to execute a stored procedure and a flat file source to have the result into .csv format file.
One of the result columns is Date.
The stored procedure returns dd/mm/yyyy but when I look at the .csv destination file, it shows the column as dd/mm/yyyy 00:00:00
Question:
How do I get rid of the 0's after the date in the destination .csv file please?
Lookup the CONVERT function in Books On Line. That'll do it for you.
This is the sql I am using in stored proc:
AsOfDate = convert(varchar(11),AsOfDate,103)
It returns something like dd/mm/yyyy but in th edestination .csv I get dd/mm/yyyy 00:00:0000
I tried using derived column but that doe snot seem to do it.
Any suggestions please?
Thanks
July 21, 2014 at 12:24 pm
Ah, my apologies. You said that in your original post and I was in too much of a hurry. I'm not sure how to do this on the SSIS side of the export. There should be something in SSIS that says use the result set of your proc as all text data instead of converting the text (dates) back to a datetime.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2014 at 8:16 am
You shouldn't need a Derived Column transformation to do this. If you click on 'Advanced' in your Flat File Connection Manager Editor, what DataType is the receiving column set to? It should be DT_STR and the OutputColumnWidth property should be set to 11.
Regards
Lempster
July 23, 2014 at 7:52 am
Lempster (7/22/2014)
You shouldn't need a Derived Column transformation to do this. If you click on 'Advanced' in your Flat File Connection Manager Editor, what DataType is the receiving column set to? It should be DT_STR and the OutputColumnWidth property should be set to 11.Regards
Lempster
I did that but in preview I can still see the leading 00000
And also the 00000 appear in the .csv file in column AsOfDate.
Any suggestions?
Thanks
July 23, 2014 at 10:21 am
You didn't answer my question. Is the DataType property for that column set to DT_STR?
July 23, 2014 at 11:03 am
Lempster (7/23/2014)
You didn't answer my question. Is the DataType property for that column set to DT_STR?
DT_STR
July 24, 2014 at 2:30 am
Hmm, in that case, I don't know why you are seeing that behavior. I put together a quick SSIS package to do the same as you are doing in your package and my dates in the CSV output file don't have any time portion. Could it be to do with the Regional Settings on your pc/server I wonder?
Sorry, I don't think I can help any further. :unsure:
July 24, 2014 at 4:13 am
Lempster (7/24/2014)
Hmm, in that case, I don't know why you are seeing that behavior. I put together a quick SSIS package to do the same as you are doing in your package and my dates in the CSV output file don't have any time portion. Could it be to do with the Regional Settings on your pc/server I wonder?Sorry, I don't think I can help any further. :unsure:
Thank you
July 29, 2014 at 5:33 pm
You can get around this by creating a new column of type DT_DBDATE and using this new column to write to the CSV file.
First, add a new derived column transformation between your source and destination.
Next, add a new column to the derived column transformation. In the Expression field, use something similar to the following: (DT_DBDate)MyOriginalDateColumn. Replace MyOriginalDateColumn in this sample with the name of your date column from the source query. Use the <add as new column> selection to be sure this is a new column.
Finally, in your flat file destination component, use the new date field you just created in place of the original date field from the source.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply