July 30, 2009 at 9:51 am
I have files coming from several clients, the trailers rec count has to be validated against the detail record count. Each client may send as many files as needed.
I am using a For Each loop to pull the files in. I have defaulted a run_date (as current date) into the both hdr recs and dtl recs. I am also storing the filename in each record so I can tell which detail goes with which trailer.
Using a Derived Col task I am inserting a 1 byte field to id the client or origin (ie. F = FAO Schwartz). Based on that I am counting the number of occurrences for each client in my procedure and using a While loop to read and validate each trailer. The problem is after processing the first trailer how do I move to the second trailer?
I guess I could use a Cursor. OR is it possible to add a derived column and add a sequence number to each hdr as it is read in? For instance use a variable in my pkg assign the variable the value of 0 and each time a trailer is read add 1 to the variable and place that value in the Derived Col.
I hope that all made sense.
Thanx,
Trudye
July 30, 2009 at 11:39 am
Hi Trudye,
it would help a lot if you could set up some sample data (DDL for table, insert statement for samples), the expected result based on the sample data and what you've tried so far.
From what you've described it doesn't look like a cursor is needed. But to verify I'd like to have some data to test against.
Most of us prefer to provide validated code, which requires some more information than currently available.
Please see the link in my signature on how to post sample data.
July 30, 2009 at 12:18 pm
You are using SSIS for this, right?
If so, each iteration of your for each loop should process one file. In your data flow for the file, you can use the RowCount transformation to get the # of rows in the pipeline and compare that to your trailer record's row count value. If the values do not match, don't insert the rows. Make sense?
July 30, 2009 at 7:09 pm
Thanks SSCrazy so much for responding.
I was with you right up to RowCount Transformation. How can I compare the row count to the trailer record count field while the trailer is still in the pipeline? And after it is out of the pipeline how can I communicate with what is in the pkg?
Probably not really smart questions but really important for me to understand what sounds like a really good idea.
August 3, 2009 at 10:00 am
Sorry for the delayed response. The RowCount transformation allows you to read in the # of rows in the data flow and place that value into a variable (of your choice). You can then take that variable and use the Derived Column transformation to add the RowCount into your data flow. Once it is in the data flow, you can compare the RowCount value to the trailer value to validate your file. You can do the comparison in the Conditional Split transformation. Create 2 outputs, one that sends the contents of the pipeline to the destination table for the condition where the Trailer = RowCount. The second output will be for when the Trailer RowCount. Use this output to handle your invalid file error. Make sense?
August 9, 2009 at 8:13 am
Thank you so very much SSCrazy for taking your valuable time to explain that. In fact that was exactly what I ended up doing. I used the Split & dervied col.
Thanx again for hanging in there with me.
Trudye
August 10, 2009 at 10:18 am
I'm glad you posted a reply. I was sure that I had been discussing the same type of tactic in 2 different threads. I found that there is a catch to using the Row Count transformation in conjunction with the Derived Column & Conditional Split. There's a timing issue with how the Row Count transformation populates the variable so you'll not get the results you really want when you add the Row Count variable into your data flow. I've come up with another solution to this using the Sort transformation to 'halt' the processing of the pipeline.
This is a fairly long thread, but you'll see the problem that I've described above along with a working solution that I've tested out.
http://www.sqlservercentral.com/Forums/Topic761855-148-1.aspx
August 10, 2009 at 5:56 pm
Thanx again SScrazy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply