October 18, 2009 at 11:18 pm
Hi,
i have a collection of tab delimited flat files & 2or 3 EXCEL files (Foramt of all the Flat files is consistent) am able to import all the columns from these files into an OLE DB destination table, by changing the datatypes + lenght in the Flat file connection manager Advanced tab, so they match the destination table. (Have used a foreach loop to load multple falt files into my staging table)
THe onley problem i am having is when i try to move across one particular column from the flat file, this column is populated with DOB info in the following format "21NOV1974:00:00:00" or spaces " " i want this changed to ([DT_DATE] with ssis)
date datatype 1974-11-21 so i can append this into my corresponding DATE field in the OLE DB Staging table. I have tried numerous things (cahnge dattype in th falt file connection manager,derived column casting etc)but still get an casting error in SSIS???
With excel fie iam having the same issues, DOB field in the EXCEl file is " " empty at this stage (but in future fiels this field will be populated with date format as above) but i still want it copied to OLE DB Staging table as a date field, however when i try to convert it to a [DT_DATE] with ssis there are erros???
October 19, 2009 at 7:56 am
Sounds like you are going to need a derived column transform or a script component to manipulate the date to a format that SSIS can understand is a date. The issue looks like you don't have any spaces between the different parts of the date (Day, Month, Year). If you use the string manipulation functions to get a format like this "24 Nov 1974 00:00:00" you should be able to convert it to a DT_DATE.
Try something and post the code here and I'll try to help out with any questions or problems that may arise as you try it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 15, 2009 at 10:50 pm
Thank you Jack,
Sorry forgot to thank you for your advise, using a derived colum to format date fields from source flatfile as : 22 NOV 1980 and then casting this as date works perfect.
Left this issue to the end of of project. works good.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply