September 29, 2011 at 12:35 pm
compare two result sets based on a single column in ssis?
Here is what I am trying to get accomplished...
I am importing 9 txt files into 9 tables...After these get imported, I want to validate that all records got imported from the source file...Within the source file there is a trailer record that has the record count in it and I pull that out and load it into another table...
So basically I would like to get the count from a table, compare it yo the record count that is in another table, if they match, go to the next step in my ssis package, if it fails, send me an email letting me know the count is messed up...
Query to get count from tables im importing from the files:
SELECT COUNT(*) FROM Table A
Query to get record count from trailer record:
SELECT record_count FROM Meta table
How can I compare these and make sure they are equal in SSIS? Or is there even a way to do that?
Thanks in advance...
September 29, 2011 at 12:37 pm
I'm not sure how I'd do that directly in SSIS, but it would be trivial to do in T-SQL, and if the data is already in tables, you could do it in an Execute SQL Task object.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2011 at 12:39 pm
Please explain...
September 29, 2011 at 12:41 pm
CREATE PROC dbo.RecordCountCheck
(@PassFail_out BIT OUTPUT)
AS
SELECT @PassFail_out =
CASE WHEN (SELECT COUNT(*) FROM Table A) = (SELECT record_count FROM Meta table) THEN 1
ELSE 0 ;
Lots of other ways to do it, that's just one idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2011 at 12:45 pm
Thanks
September 29, 2011 at 1:10 pm
Ok that query works great...Now to implement that into SSIS is the hard part...
I know you can put Execute SQL Task in my package...And I can put that query into that task...
However, how can I implement it where if the query returns a 1, go to the next step in the package and if it returns 0, fail the package and send me an email?
September 29, 2011 at 1:21 pm
I don't think I have the full picture, but here's how I would do it, based on what you've said:
Step 1: Import Records
For each one of your files, grab the file, import it into the SQL Server table. You say that the trailing record in your file is the rowcount - what you can easily do then, is have a step that takes place before your OLE DB Destination, which is a conditional step - if the conditional step detects that the particular column contains a row count, then run a SQL Command to insert the value into your rowcount table, if not, then insert that row into your OLE DB Destination table. Also, put a rowcount transformation into your process, after the conditional, and before the OLE DB destination, on the path leading to the OLE DB Destination, and store the rowcount as a variable. Then, after your Data Flow Task, run an Execute SQL task to insert into the rowcount table, the actual number of rows inserted. Then, repeat for the next file.
In other words:
1. a. Flat File Source
1. b. [any work that needs to be done to the flat file]
1. c. Conditional Split
1. d. IF column == rowcount -->
1. d. 1. Insert record into rowcount table
1. e. IF column != rowcount -->
1. e. 1. Rowcount Transform --> store rowcount in variable
1. e. 2. OLE DB Destination
So, in the end, you would have ten tables - nine of them would be the file contents of the nine files, and the tenth table would be your rowcount table. The rowcount table would contain three columns - one column contains the name of the text file/table being analysed, one contains the value in the text file which has the row count of the text file, and one contains the row count produced by the row count transformation, indicating how many rows were written to the database.
Step 2: Compare Records
Next, what you would do, is have a FOREACH loop, using an ADO enumerator. You would loop through all the records in your rowcount table. For each row in that table, you would store the three columns into variables, and then compare the two rowcount variables. If they are equal, you continue.
In order to get the failure result, you can do your comparison using a Script Task. The script task will take as an input the two rowcount variables. If the variables are equal, you put Dts.Success, and if they are not equal, you put Dts.Failure, and use the script task to send an email. See this thread for info on how to do that:
September 30, 2011 at 7:28 am
If you go to the properties on a flow (green or red arrow in the package designer), you can set it to check for values. If you run the proc, and assign the output value to a variable, you can have the flow from that step go down one path for 1 and another for 0.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply