Need help using variables from a procedure in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Need help using variables from a procedure in SSIS

Author

Topic

surefooted
Posting Yak Master

188 Posts

Posted - 2006-08-14 : 15:40:59

I am converting converting DTS packages to SSIS. In DTS we use a procedure to return pertinent information to a package by using a stored procedure to set package variables. I have hit a wall in that I cannot get this to work. I have searched all over trying to find the information to get this to work. Here is what I have:

Create Table TableWithVariables (
	PackageID int Identity,
	DTSPackageName varchar(50),
	SourceSystem varchar(50),
	SouceTable varchar(50),
		Constraint PK_TableWithVariables Primary Key NonClustered 
			( 
				PackageID ASC
			)
)

Insert TableWithVariables (DTSPackageName,SourceSystem,SouceTable)
Select 'Package1', 'Source1', 'Table1' union all
Select 'Package2', 'Source2', 'Table2' union all
Select 'Package3', 'Source3', 'Table3' union all
Select 'Package4', 'Source4', 'Table4'


CREATE PROCEDURE dbo.upGetDTSPackageVariables
		@Package varchar(80),
		@PackageName varchar(250) OUTPUT,
		@SourceSystem varchar(250) OUTPUT,
		@SourceTable varchar(250) OUTPUT,
		@CurrentDate datetime OUTPUT
  
AS  
  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
    

Select  @PackageName = PackageName,  
		@SourceSystem = SourceSystem,  
		@SourceTable = SouceTable,  
		@CurrentDate = getdate()  
From	DTSPackageVariables  
Where	PackageName = @Package  

RETURN
GO

I have been using


Exec upGetDTSPackageVariables2 ?, ? Output, ? Output, ? Output, ? Output

Under the general tab, I have:

ResultSet - none
ConnectionType - OLE DB
Connection - "ConnectionName"
SQLSourceType - Direct Input
SQL Statement - Exec upGetDTSPackageVariables2 ?, ? Output, ? Output, ? Output, ? Output

I have the parameter mapping set with the following:

[code]
User::DTSPackge (String) | Input | Varchar | 0
User::Packagename (String) | Output | Varchar | 1
User::SourceSystem (String) | Output | Varchar | 2
User::SourceTable (String) | Output | Varchar | 3
User::CurrentDate (DateTime) | Output | Date | 4
[/Code]

I need to return the values of upGetDTSPackageVariables to variables in the SSIS package. It is not working. I keep getting the error, "Error HRESULT E_FAIL has been returned from a call to a COM component." What am I doing wrong?

-Jon
Should still be a "Starting Member"

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-08-14 : 15:57:35

it looks fine. I do notice the create proc you gave us specifies: upGetDTSPackageVariables
, whereas your execs are:
Exec upGetDTSPackageVariables2 ?, ? Output, ? Output, ? Output, ? Output

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

surefooted
Posting Yak Master

188 Posts

Posted - 2006-08-14 : 17:29:59

Yea, I meant to not have the 2, but you say it looks fine, but I keep getting errors.

-Jon
Should still be a "Starting Member"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-14 : 17:32:52

The best resource for DTS and SSIS is sqldts.com/sqlis.com. They probably have examples of what you are trying to do there already.

Tara Kizer

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 20:51:58

Why don't you use the value in the table to set the variables in the package when you load it rather than when it runs - that would probably be a lot easier for dts and ssis.
In ssis you can also use a package configuration which it can get from a table - although I prefer an xml file referenced by an environment variable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources