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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
nmcdermaidCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.