Contingent execution based on stored procedure results I(zz)

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/18/9178.aspx
Well, I've been a little pre-occupied lately. Between rebuilding machines, my kids getting sick, getting sick from my kids, and my day job, I haven't been blogging for a while. So I've got a few stored up. Look out, here they come. :)

Here's one you might find interesting. Say you have the following requirements:

• You have a stored procedure that queries for some state on your server.
• You want to conditionally execute portions of your package based on the results of the stored procedure.

For this particular case, the user wants to determine if a given table exists and if it does, if it is a user table.

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[AWBuildVersion]')) and


OBJECTPROPERTY(id, N'IsUserTable') = 1


 
So, the problem is, how to execute the query and get the results back into SSIS.
You've got a few options here. Return the result in a result set and iterate over it in a ForEach Loop with the ADO enumerator or such. 
But, for this case, I'm only interested in the one value. 

Here's how I addressed this and it's pretty straightforward. 

I created a stored procedure like this: 

create procedure [dbo].[uspGetUserTableExists](


@TableName varchar(256)

)

AS 

BEGIN 

 DECLARE @FlExists bit 

 IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@TableName+']') 

  and OBJECTPROPERTY(id, N'IsUserTable') = 1) 


 BEGIN 

  SET @FlExists = 1 

 END 

 ELSE 

 BEGIN 

  SET @FlExists = 0

 END


 SELECT @FlExists AS 'Exists' 


END




 

The stored procedure just checks for the existence of the table and then if the table is a user table and sets a flag based on that. 

The select statement forces a return set and forces the column name 'Exists' which will make it a little clearer later on what the return value is when we use it in the package.

Now that I have a stored procedure to call, I can call it in my package with an Execute SQL Task. I've created a simple package that calls the stored procedure and based on the return value, executes a different script task.

The package looks like this:

 

The Execute SQL Task calls the stored procedure passing in the name of the table to find by binding the TableName variable to the parameter of the stored procedure. That looks something like this:

 

See the question mark following the SQLStatement? That is the parameter. This is how you call a stored procedure with a parameter from the SQL Task. Next, we need to map the parameter to the input variable. You do that on the Parameter Mapping tab, shown here:

The TableName variable is a string variable that holds the name of a... table. Once you've gotten a value into a variable in SSIS, it's a simple matter to use it everywhere in the package. Variables have always been important in DTS, but they're crucial in IS. So, for example, this “package part” could be used as sublogic to determine if certain tables should be backed up based on a heuristic of some sort that keys off whether the table is a user table or not. The names of the tables could come from the SMO enumerator in a ForEach loop etc. The graphic above shows how to get the name of the table into the input parameter for the stored procedure. I can do this directly with the SMO enumerator and the script task, but like most scenarios in SSIS, there are many ways to accomplish this. What is the best way depends on your requirements.

OK, this post is getting pretty long. That's enough for now I think.

In the next post, I'll show you how you can get the return value from the SQL statement back into a variable, and how to use the return value in expressions on precedence constraints to conditionally execute different parts of the package. I'll also post the package so you can play around with it yourself.

Hope this is interesting and helpful. Please let me know if it's otherwise. :)

Thanks,

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden