October 7, 2011 at 11:09 am
Hey guys,
Background: I have a SSIS package that loads data...And I need to verify that the data that gets loaded matches what is in the source file...And I did that successfully...But that is just the first part of it...Next I need to verify that the data that gets loaded throughout multiple tables also matches a count...Here are the tables: Staging, Drops, Production...Data gets loaded into Staging, gets verified with source file, then PK checks and business checks runs and if something is wrong with the data, it gets populated into the Drops table, then the good data gets loaded into the Proudction table...
So with that being said, here is the formula bc records get appended to the Production table so when we get a new years worth of data that gets added...So to verify a files load, I have to verify staging = source (which I have completed), then I have to set a variable = count of production table BEFORE the new load takes place (I have accomplished this), but what I keep getting an error when I verify that Production AFTER COUNT - Production BEFORE COUNT + DROPs table count = Staging
So I created a Production BEFORE COUNT variable in an Execute SQL Task: SELECT COUNT(*) as ProductionBeforeCount FROM Production table, I set the Result Set to Single Line and then in the ResultSet tab, I set the ResultSet (ProductionBeforeCount) = ProductionBeforeCount variable...And that worked...
However when I load the data in the package, I need to verify using the formula I mentioned above...Again I used a Execute SQL Task:
DECLARE @Verification BIT
SET @Verification =
CASE WHEN (((SELECT COUNT(*) FROM Production) - @ProductionBeforeCount) + (SELECT COUNT(*) FROM Drops)) = (SELECT COUNT(*) FROM STAGING) THEN 1
ELSE 0 END
IF @Verification = 0
BEGIN
RAISERROR('Count doest match',16,1)
END
And in the Parameter Mapping tab, I set the variable as input
But it blows up everytime and not with Count doesnt match error...Error is that parameter name is unrecognized...Can anyone tell me what I am doing wrong...Basically I am wanting to know how I can use the ProductionBeforeCount in my query?
I know this is a lot and can come off as confusing so if anyone is willing to throw me some ideas and needs more clarification, I will gladly appreciate it!
Thanks
October 7, 2011 at 11:59 am
As a possible suggestion, maybe try putting in a few Execute SQL tasks instead of trying to do it all in one?
Have three execute SQL Tasks:
SELECT COUNT(*) AS 'ProdCount' FROM Production
SELECT COUNT(*) AS 'DropCount' FROM Drops
SELECT COUNT(*) AS 'StagingCount' FROM STAGING
Then put in a script task, and handle the evaluation of your results, passing in four variables, namely, the three from above and the one from your "Production BEFORE COUNT"
October 7, 2011 at 12:18 pm
Well I got past the parameter is unrecognizable error...I had to put a 0 in the Parameter name in the Parameter Mapping tab...However now I am getting the error, Must declare variable @ProductionBeforeCount...
But I have that set in a previous task...And I have that variable as a variable for the whole package...Does it not carry over to other tasks in the package if I try to call it?
October 7, 2011 at 12:19 pm
Are you trying to write an Execute SQL task and using @ProductionBeforeCount directly?
If so, it won't work, because it doesn't exist within the scope of that task.
What you can do instead is have your task be executed from a variable, and have your variable contain the expression which is your whole query. The query would be built as a string, and then you can add @ProductionBeforeCount in.
October 7, 2011 at 12:55 pm
I got it...
I had to enter a 0 in the Parameter name field in the Parameter Mapping and then in the query where I call that variable, instead of using the @ProductionBeforeCount, I had to use ?...And that worked fine!
October 7, 2011 at 1:35 pm
Yeah that will work provided your input query isn't anything complex.
But if you start having more complex queries in your execute SQL statement, you may encounter an error because the compiler won't know which parameter goes with which input. In that case you'll need to use the query from variable.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply