March 17, 2010 at 8:06 am
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
March 17, 2010 at 8:17 am
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?
March 17, 2010 at 8:35 am
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...
March 17, 2010 at 8:55 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 18, 2010 at 3:21 am
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
March 18, 2010 at 4:41 am
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
March 19, 2010 at 8:27 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply