Flat File Source and Flat File Destination & Convert Dates to text mm/dd/yyyy

Question

Flat File Source and Flat File Destination & Convert Dates to text mm/dd/yyyy

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 9, 2014 at 11:45 am

#148130

I'm getting a flat file from an AS400 Vendor with no column headers.
The Date Columns are in a Date Format.
I need to send the data to an Oracle vendor but they was the dates in a character format mm/dd/yyyy without headers.
How can I do this without having to import it into SQL Server?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

You read the file with SSIS, do the transformations with a derived column and write the data back to a file.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

:crazy:

Koen Verbeeck (1/9/2014)
You read the file with SSIS, do the transformations with a derived column and write the data back to a file.

I was thinking that it might have been a data conversion task, my bad.

I have used derived columns before.

What would the SSIS Syntax and could you tell me in a little more detail the steps that I take to accomplish this if not too much trouble?

Thank you for your help. 🙂

Edit: I hate when I have to use delimited files without column headers. Unfortunately I have no control over this.:crazy:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Can you give an example of how the source data looks like and how you want it formatted?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

What would the SSIS Syntax and could you tell me in a little more detail the steps that I take to accomplish this if not too much trouble?

Can you expand a little? I'm not sure which bit you are asking for help with.

The basic flow is straightforward:

Flat File Source --> Transformations --> Flat File Target

If you are asking for help with specific derived column transformations, please include the exact source and target formats.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I hope that this is enough information.

Source:

6600154038120140301REN|XO|XO||FL|81|660015403|2010-03-01 00:00:00|03/01/2014|12|

Destination Desired Output:

6600154038120140301REN|XO|XO||FL|81|660015403|03/01/2010|03/01/2014|

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

I tried using the following expression and it is not valid syntax. Can not parse.

I had to change the Column from Inception which I defined to Column 8 as well.

(DT_WSTR, 2)MONTH( Column 8 ) + "/" + (DT_WSTR, 2)DAY( Column 8) + "/" + (DT_WSTR, 4)YEAR( Column 8)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ells SSCrazy Eights Points: 9901 More actions · Answer 7

SUBSTRING(COLDB,6,2) + "-" + SUBSTRING(COLDB,9,2) + "-" + SUBSTRING(COLDB,1,4)

So thats the answer, now the working out.

I had assumed that in the flat file connection manager that you took in the date column as a string (if the data type is DB_DATE or database Date then the above will not work)

Then add a derived column task. Add a new column to the data flow and insert the code above (swapping COLDB for whatever you called your column in the flatfile connection manager)

Alternatively if you have it as a date in the flat file connection then

SUBSTRING((DT_WSTR,50)COLDB,6,2)

would allow you to convert it to a string and then take a chunk out of the string with SUBSTRING

Hope that helps

E

😎

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Thanks for the replies.

The following worked:

SUBSTRING(Inception,6,2) + "/" + SUBSTRING(Inception,9,2) + "/" + SUBSTRING(Inception,1,4)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/