Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

format the output column in destination .csv Expand / Collapse
Author
Message
Posted Monday, July 21, 2014 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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?
Post #1594588
Posted Monday, July 21, 2014 9:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:59 PM
Points: 35,572, Visits: 32,165
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594638
Posted Monday, July 21, 2014 9:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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
Post #1594640
Posted Monday, July 21, 2014 12:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:59 PM
Points: 35,572, Visits: 32,165
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1594763
Posted Tuesday, July 22, 2014 8:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:37 AM
Points: 2,127, Visits: 1,485
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
Post #1595099
Posted Wednesday, July 23, 2014 7:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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
Post #1595491
Posted Wednesday, July 23, 2014 10:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:37 AM
Points: 2,127, Visits: 1,485
You didn't answer my question. Is the DataType property for that column set to DT_STR?
Post #1595579
Posted Wednesday, July 23, 2014 11:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
Lempster (7/23/2014)
You didn't answer my question. Is the DataType property for that column set to DT_STR?

DT_STR
Post #1595603
Posted Thursday, July 24, 2014 2:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 21, 2014 7:37 AM
Points: 2,127, Visits: 1,485
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.
Post #1595814
Posted Thursday, July 24, 2014 4:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:28 AM
Points: 155, Visits: 202
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.

Thank you
Post #1595841
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse