I have an SSIS package that I am writing to replace a very long winded process which uses an access 97 (would you believe it) which also links to and updates DBF files and it's fair to say I'm struggling, the DBF files do have to stay and have to be output in the same format. The issue I'm facing at the moment is that I have an execute SQL Task that runs a stored procedure to basically checks the integrity of various tables, if any of the statements in the stored procedure come back with a value the SSIS package needs to stop and the user needs to manually check various things.
I've set the stored procedure with the output variables see below:
I've also set the SSIS package to accept output parameters; see below. In case you were wondering the only way I could get the step to run in SSIS was if the @Total variable was varchar, it didn't seem to like INT, Float or anything else I tried.
My issue is that if the @Total field comes back with anything > 0 then I need to stop the package and show the output from @Output and I can't find anywhere how to do this.
Any ideas greatfully received, if I've gone about this completely the wrong way I'm more than happy to start again.
Many many thanks.: /upfiles/SD2.bmp
asked Mar 30, 2011 at 08:06 AM in Default
First of all, have a look at this page, http://msdn.microsoft.com/en-us/library/ms141036.aspx it shows the mapping between sql and the parameters, for a SQL int, use a long etc.
You can use precendence constraints for this. As long as your parameters are getting the correct output from the SP, add two script tasks to the control flow after the execute SQL task. Create a connection to both the script tasks, and click on the first connection.
Change the EvalOp value to ExpressionAndConstraint and click on the control and on the connection again(for some reason my 2005 version requires this). Now in the expression value, enter your variable like this (add an @ symbol to the front of your variable)
this constraint will obviously be your stop point, then you can use the script task (or in production send en email) to notify the user of the error
And on the second connection do the same again, this time enter the expression as
This "side" of the package will continue as normal if there are no errors.
answered Mar 30, 2011 at 04:45 PM