How can I default null values to parameters in an SQL Task being
executed from a DTS package?
The task executes a stored procedure that expects a number of
parameters as shown below:
CREATE PROCEDURE [CT_IMPORT_RATES]
(@clause_id int,
@table varchar(20),
@startrg varchar(10) =NULL,
@endrg varchar(10) =NULL,
@overwrite bit=0,
@Ret_Code as int = NULL OUTPUT)

All the above parameters are set up as Global variables in the DTS
package. This package in turn is executed by another stored procedure
which is shown below:

CREATE PROCEDURE dbo.CT_EXEC_IMPORT_PACKAGE
(
@clause_id int,
@table varchar(20),
@startrg varchar(10) =NULL,
@endrg varchar(10) =NULL,
@overwrite bit=0,
@gvFileLocation varchar(200),
@Ret_Code as int = NULL OUTPUT
)
AS
DECLARE @oPKG int, @hr int
DECLARE @object int
DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
--BEGIN
-- PRINT '*** Create Package object failed'
-- EXEC sp_displayoaerrorinfo @oPKG, @hr
-- RETURN
--END
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END

-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("(local)", "", "", 256, , , , "ImportCPTRates")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
--Set a Global Variable:
EXEC @hr = sp_OASetProperty @oPKG,
'GlobalVariables("gv_clause_id").Value', @clause_id
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
EXEC @hr = sp_OASetProperty @oPKG,
'GlobalVariables("gvTable").Value', @table
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
IF @startrg IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @oPKG,
'GlobalVariables("gvstartrg").Value', @startrg
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src,
Description=@desc
SET @Ret_Code=@hr
RETURN
END
END
IF @endrg IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @oPKG,
'GlobalVariables("gvendrg").Value', @endrg
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src,
Description=@desc
SET @Ret_Code=@hr
RETURN
END
END
EXEC @hr = sp_OASetProperty @oPKG,
'GlobalVariables("gvoverwrite").Value', @overwrite
IF @hr <> 0
BEGIN
PRINT '*** GlobalVariable Assignment Failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
-- Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
SET @Ret_Code=@hr
RETURN
END
GO

The way I got it to work is such a hack!!
If I do not assign a value to a parameter, it actually gets passed the
string <not displayable> -- which is what is you see in the Global
Variables tab of the package properties window. There must be a better
way to do it, especially since the variable in my stored procedure if
of type varchar(10) so I actuallly am doing this :
if (@startrg <>'<not displ') -- validate against the first 10
characters


amcniw (AT) yahoo (DOT) com (Rathtap) wrote in message news:<b21a5958.0307091003.4ecc7150 (AT) posting (DOT) google.com>...