pass package name to sproc

Question

Post reply

pass package name to sproc

sqlfriend

SSC Guru

Points: 52794
More actions
December 5, 2011 at 1:58 pm

#247887

I created a ssis package to do import by using SQL task , and it calls a stored procedure that basically a merge statement to compare data to insert and update.
In my destination table, I have a createdby column, I want the packagename to be populated.
So it is nice that the package name variable [System::PackageName] can be passed as a parameter to call the sproc, then in the sproc I can use insert createdby= packagename.
How can I achieve this?
Thanks

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Just add a parameter to the sproc for that data and map the package variable into the parameter.

CEWII

sqlfriend SSC Guru Points: 52794 More actions · Answer 2

Thanks, I have added the parameter to stored procedure, but I cannot make the ssis part correct, can you tell me a little in detail how I can make this work.

I use the following in SQL task- SQL Statement direct import, but it gives error.

Declare @packagename=[System::PackageName]

Exec dbo.domProductCategory_LoadMerge

@packagename

Go

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Assuming you are using a Exec SQL task.

Command would be:

EXEC dbo.domProductCategory_LoadMerge ?

GO

On the Exec SQL Task, Parameter Mapping Tab, click add

For Variable Name Select: System::PackageName

Direction: Input

Data Type: varchar

Paramter Name: 0 << The number ZERO

Click OK

It is now mapped.

CEWII

sqlfriend SSC Guru Points: 52794 More actions · Answer 4

Elliott Whitlow (12/5/2011)
Assuming you are using a Exec SQL task.
Command would be:
EXEC dbo.domProductCategory_LoadMerge ?
GO
On the Exec SQL Task, Parameter Mapping Tab, click add
For Variable Name Select: System::PackageName
Direction: Input
Data Type: varchar
Paramter Name: 0 << The number ZERO
Click OK
It is now mapped.
CEWII

Yeh, it works perfectly. One more question, so the parameter starts with 0, if there are a second parameter, I will put 1, is that correct? and I also see a parameter size with -1 there, I don't need to change that,even it has two parameters, is that corect?

Thanks