SSIS flat file convert

I am converting a flat file as fixed width using ssis. I have the package built but instead of running a sql after the package builds the table, I would like to do all the cast and converts in the package.

How in the ssis package can I do a convert of varchar(6) to datetime?  121806 to 12/18/06
How about money datatype?  000012345 to 0000123.45

Can this be done in the create table part of the package?


whargraAsked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
Here it is reproduced (ntoe this is for YYYYMMDD forrmat). The only way to do it as part of the create table script is to create a calculated column in your table.... not very robust.


Converting a String Date
Converting a string date in the form YYYYMMDD to a date

This question comes up a lot. How to convert a date in string format to a date type.

You can write a simple derived column expression to parse this out and convert it to a date. You can set the derived column to replace the string date column or create a new one.

Here's the expression to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date
 Derived Column 1
 
20050112
 1/12/05
 
20031122
 11/22/03
 
20050509
 5/9/05
 
20010101
 1/1/01
 
20000301
 3/1/00
 
20021003
 10/3/02
 
20022002
 2/20/02
 
19631003
 10/3/63
 
19621002
 10/2/62
 
20051111
 11/11/05
 

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

 

Post a Comment ::
Comments
# re: Converting a String Date @ Saturday, July 16, 2005 8:09 AM
Why wouldn't you just cast the string to a datetime?

SELECT CAST('20050112' as DATETIME).

Seems like a lot of effort for no gain. Or am i missing something
Clayton Firth

# re: Converting a String Date @ Saturday, July 16, 2005 8:12 AM
Why wouldn't you just cast the string to a datetime?

SELECT CAST('20050112' as DATETIME).

Seems like a lot of effort for no gain. Or am i missing something?
Clayton Firth

# re: Converting a String Date @ Monday, July 18, 2005 7:28 PM
why not just use this?

print convert(varchar(12),CAST('20050112' as DATETIME),101)

i think this is a better approach than what you are doing...
keith rull

# re: Converting a String Date @ Monday, July 18, 2005 10:15 PM
I think some giving comments may not be aware that the solution is proposed for the expression evaluator in SSIS. The proposed simple solutions are not valid there.
Knight_Reign

0
 
nmcdermaidCommented:
I hate to just be a lazy link poster but here's an example for your date issue:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/14/16073.aspx

You drop this derived column transformation between your source file and your detstination table.

You can use the same transformation to change your money datatype too.
0
 
whargraAuthor Commented:
Thanks for the link though didn't work so I will just continue with the after transform sql.
0
All Courses

From novice to tech pro — start learning today.