x

More SSIS Help please using Output Parameters and Variables

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:

alter PROC SX_CheckLookup   @Output as varchar(500) Output, @Total as varchar(10) Output
as  
/*  
Stored procedure to check the contents of various tables.  If comes back with records then raiserror to stop SSIS and get user to check the details  
Created by:  Annette Allen  
Created on: March 2011  

  ** Test Script **
  Declare @Output as varchar(500)
Declare @Total as int 
  exec SX_CheckLookup @Output output, @Total output

*/  
declare @Outcome as varchar(500)  
declare @Count as int
declare @Total1 as int
--declare @Total as int

set @Count = (  
select Count(*)  
from (select distinct MULTI_CODE FROM VAMPEMP AS X WHERE NOT EXISTS  
(  
SELECT DRUGCODE FROM DRUGEMP WHERE DRUGCODE = X.MULTI_CODE) ) as x  
)  

 set @Total1 = isnull(@Total,0) + isnull(@Count,0)
set @Outcome= (  
select case when @Outcome is null and @Count > 0 then 'Multicode'   
when @Outcome is null and @Count = 0 then 'No records'  
else @Outcome + '; Multicode' end)  

--print @Count  
--print @Outcome  

set @Count = (  
select Count(*)  
from (select * FROM BNFEMP AS X WHERE NOT EXISTS(  
SELECT * FROM DRUGEMP WHERE DRUGCODE = X.DRUGCODE)) as x)  
  set @Total1 = isnull(@Total,0) + isnull(@Count,0)
set @Outcome= (  
select case when @Outcome is null and @Count > 0 then 'BNFEMP'   
when @Outcome is null and @Count = 0 then 'No records'  
when @Outcome is not null and @Count = 0 then @Outcome  
else @Outcome + '; BNFEMP' end)  

--print @Outcome  

set @Count = (  
select Count(*)  
from (select * FROM MANDEMP AS X WHERE NOT EXISTS(  
SELECT * FROM DRUGEMP WHERE DRUGCODE = X.DRUGCODE)) as x)  
  set @Total1 = isnull(@Total,0) + isnull(@Count,0)
set @Outcome= (  
select case when @Outcome is null and @Count > 0 then 'MANDEMP'   
when @Outcome is null and @Count = 0 then 'No records'  
when @Outcome is not null and @Count = 0 then @Outcome  
else @Outcome + '; MANDEMP' end)  

--PRINT @Outcome  

set @Count = (  
select Count(*)  
from (select * FROM PACKEMP AS X WHERE NOT EXISTS(  
SELECT * FROM DRUGEMP WHERE DRUGCODE = X.DRUGCODE)) as x)  
  set @Total = isnull(@Total,0) + isnull(@Count,0)
set @Outcome= (  
select case when @Outcome is null and @Count > 0 then 'PACKEMP'   
when @Outcome is null and @Count = 0 then 'No records'  
when @Outcome is not null and @Count = 0 then @Outcome  
else @Outcome + '; PACKEMP' end)  

--PRINT @Outcome  

set @Count = (  
select Count(*)  
from (select * FROM NAMEMP AS X WHERE NOT EXISTS(  
SELECT * FROM DRUGEMP WHERE DRUGCODE = X.DRUGCODE)) as x)  
  set @Total1 = isnull(@Total,0) + isnull(@Count,0)

set @Outcome= (  
select case when @Outcome is null and @Count > 0 then 'NAMEMP'   
when @Outcome is null and @Count = 0 then 'No records'  
when @Outcome is not null and @Count = 0 then @Outcome  
else @Outcome + '; NAMEMP' end)  

--PRINT @Outcome

set @Output = @Outcome
set @Total = cast(isnull(@Total1,0) as varchar(10))

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.

alt text

![alt text][2]

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.

[2]: /upfiles/SD2.bmp
more ▼

asked Mar 30, 2011 at 08:06 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 60 62 66

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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)

@intNum >0

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

@intNum == 0

This "side" of the package will continue as normal if there are no errors.

Good luck!

more ▼

answered Mar 30, 2011 at 04:45 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

Many thanks for this, will give it a go.
Mar 31, 2011 at 12:48 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x937
x27
x18
x10

asked: Mar 30, 2011 at 08:06 AM

Seen: 2073 times

Last Updated: Mar 30, 2011 at 08:06 AM