Flat File SSIS

Question

Post reply

Flat File SSIS

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
March 17, 2010 at 8:06 am

#212537

I have many text files that I have to parse and load into a sql table (staging table ) for processing.
In the text file I have a date field in which sometimes customer is sending incorrect format for the date field. Now in SSIS I want to check the datefield values and validate them whether it meets the requirement or not..
if yes send the good data into the sql table and if it doesnt meet the criteria (or if the date format is wrong) then send the bad data into Error Table in SQL Server.
How can I do that without loading the data into sql server in other words while I am extracting the data from flat files I want to validate the field in memory >> DataFlow > Flat file source > and the check the date field > good data or bad data..
please advise

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

i would use a conditional split to spit out the rows that have invalid dates and then send them to a junk destination.

what determines a valid date, is it just the date is vaild in the format? or are there business rules that also apply?

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

Thanks Steve for your reply.

Basically there are no specific business rules...customer is sending dates in invalid format and I just need to validate them because SQL Server doesnt accept that..so they need to be in this form like ... 2010-03-17 ..which will be accepted by SQL Server table...

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

So bring the dates in as strings, and in the dataflow use a data conversion to convert to date. Then set the Error behaviour to be Redirect Row - to send the invalid rows to an error table.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 4

understood ! now my next question is the flat files are in a directory

and I have to process all the flat files from the directory one by one

how do I loop...I think I can use foreach loop container and place the data flow task in it...but the foreach loop container will iterate and place the file names in a variable...BUT the flat file source doesnt have an option to specify the variable as input..it seems its static and asks for location of the specific file..please advise

steveb. SSC-Forever Points: 46733 More actions · Answer 5

You can set almost any property of a task by using expressions, for a flat file you want to set the connection string value in the flat file connection manager, a good example of looping through files and using expressions for the connection string is here;

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx"> http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

dg227 SSCrazy Points: 2017 More actions · Answer 6

Another thing to try, without using a loop container, is the MULTIFLATFILE connection.

You'll still need to set up a connection string in its properties (which can be controlled by a variable and/or an expression), but this is a handy way to get looping-like functionality among similarly-named flat files in the data flow.