Executing DTS Package - Error not able to find the package.

I am running Sqlserver 2005 and IS.
Here is the SP I am running.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ========================================
=====
-- Author: Name
-- Create date:
-- Description:
-- ========================================
=====
ALTER PROCEDURE [dbo].[CallRiskDataDTS]
-- Add the parameters for the stored procedure here
@.loaddate datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @.DTSPackageObject int
DECLARE @.HRESULT int
DECLARE @.property varchar(255)
DECLARE @.return int
DECLARE @.ErrDescrip varchar(255)
DECLARE @.ErrSource varchar(30)
DECLARE @.ErrHelpId int
DECLARE @.ErrHFile varchar(255)
DECLARE @.ErrMsg varchar(255)
Declare @.FileFilter varchar(8)
DECLARE @.sDTSPackagePath varchar(1000)
DECLARE @.sDTSSpecialUser varchar(50)
if not IsDate(@.loaddate) = 0
Begin
select @.loaddate = getdate()
End
select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(mm,@.loaddate)))),'') +
convert(varchar(2), Datepart(mm,@.loaddate)) +
isnull(replicate('0', 2 - len(convert(varchar(2),
Datepart(dd,@.loaddate)))),'') +
convert(varchar(2), Datepart(dd,@.loaddate))
SELECT @.ErrMsg = 'Error running DTS package'
-- Create a DTS Package object
EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR (@.ErrMsg,11,1)
RETURN
END
EXEC @.HRESULT = sp_OAMethod
@.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolData
Upload",
Nothing)', NULL --, @.flags=256,
@.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
@.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
@.PackagePassword =''
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
OUTPUT,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrDescrip,11,1)
RETURN
END
-- Set the FailOnError property to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Set
to true
EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
@.FileFilter --Set to true
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
-- Call the EXECute method
EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
IF @.HRESULT <> 0
BEGIN
EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTPUT
,@.ErrDescrip OUTPUT
RAISERROR ( @.ErrMsg,11,1)
RETURN
END
--remove the object from memory
EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
END
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
I am getting the following message
Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
{}.{}') does not exist. I want to set this up so I can execute is from an
ASP.NET page. I am unable to find the cause of it.
In my SQL Management Studio, connect to Integration services, I see the
package in STORED PACKAGES , MSDB tree.
Any help is greatly appreciated.
ThanksFor that matter I am not able execute any package, I am getting the same
error. Thanks
Raj.
"Raj25" wrote:

> I am running Sqlserver 2005 and IS.
> Here is the SP I am running.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
>
>
> -- ========================================
=====
> -- Author: Name
> -- Create date:
> -- Description:
> -- ========================================
=====
> ALTER PROCEDURE [dbo].[CallRiskDataDTS]
> -- Add the parameters for the stored procedure here
> @.loaddate datetime
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> DECLARE @.DTSPackageObject int
> DECLARE @.HRESULT int
> DECLARE @.property varchar(255)
> DECLARE @.return int
> DECLARE @.ErrDescrip varchar(255)
> DECLARE @.ErrSource varchar(30)
> DECLARE @.ErrHelpId int
> DECLARE @.ErrHFile varchar(255)
> DECLARE @.ErrMsg varchar(255)
> Declare @.FileFilter varchar(8)
> DECLARE @.sDTSPackagePath varchar(1000)
> DECLARE @.sDTSSpecialUser varchar(50)
> if not IsDate(@.loaddate) = 0
> Begin
> select @.loaddate = getdate()
> End
> select @.FileFilter = convert(varchar(4),Datepart(yyyy, @.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(mm,@.loaddate)))),'') +
> convert(varchar(2), Datepart(mm,@.loaddate)) +
> isnull(replicate('0', 2 - len(convert(varchar(2),
> Datepart(dd,@.loaddate)))),'') +
> convert(varchar(2), Datepart(dd,@.loaddate))
> SELECT @.ErrMsg = 'Error running DTS package'
> -- Create a DTS Package object
> EXEC @.HRESULT = sp_OACreate 'DTS.Package', @.DTSPackageObject OUTPUT
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR (@.ErrMsg,11,1)
> RETURN
> END
> EXEC @.HRESULT = sp_OAMethod
> @.DTSPackageObject,'LoadFromSqlServer("MYSERVER","","",256,,"","","SymbolDa
taUpload",
> Nothing)', NULL --, @.flags=256,
> @.PackageGuid='{20E6D83E-9CC3-4976-8ADE-7BEB19260A47}',
> @.PackageVersionGuid='{1CC407FF-0BBD-4013-8717-616A3C68CCB1}',
> @.PackagePassword =''
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource
> OUTPUT,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrDescrip,11,1)
> RETURN
> END
> -- Set the FailOnError property to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FailOnError', -1 --Se
t
> to true
> EXEC @.HRESULT = sp_OASetProperty @.DTSPackageObject, 'FileFilter',
> @.FileFilter --Set to true
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> -- Call the EXECute method
> EXEC @.HRESULT = sp_OAMethod @.DTSPackageObject, 'EXECute', NULL
> IF @.HRESULT <> 0
> BEGIN
> EXEC @.HRESULT = sp_OAGetErrorInfo @.DTSPackageObject,@.ErrSource OUTP
UT
> ,@.ErrDescrip OUTPUT
> RAISERROR ( @.ErrMsg,11,1)
> RETURN
> END
> --remove the object from memory
> EXEC @.HRESULT = sp_OADestroy @.DTSPackageObject
> END
>
> SET QUOTED_IDENTIFIER OFF
> SET ANSI_NULLS ON
>
> I am getting the following message
> Msg 50000, Level 11, State 1, Procedure CallRiskDataDTS, Line 77
> The specified DTS Package ('Name = 'SymbolDataUpload'; ID.VersionID =
> {}.{}') does not exist. I want to set this up so I can execute it from a
n
> ASP.NET page. I am unable to find the cause of it.
> In my SQL Management Studio, connect to Integration services, I see the
> package in STORED PACKAGES , MSDB tree.
> Any help is greatly appreciated.
> Thanks
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp