Can I do this in SSIS?

Question

Can I do this in SSIS?

Kit G

SSCrazy

Points: 2888
More actions
June 30, 2011 at 11:40 am

#243092

I want to be able to run a procedure that takes an input value and outputs a file based on that value.
As an example, you have an SSIS package that will output a file of a customer's orders. You only need one customer at a time and you want to pass the customer account number through when you execute the stored procedure.
As an example
exec dbo.OrdersByClient 45342
I've got the procedure executing the SSIS package, but I just can't figure out how to pass the input variable of "45324" to the stored procedure.
In the SSIS package, my control flow executes a stored procedure which I thought would take the input parameter and pass it alone, but doesn't cause I get an error that my varaible isn't assign, and then from there is supposed to take the result set I don't get and export it to a file.
Is it possible to send over this variable to the SSIS package when I fire it off in a stored procedure? I figure it must be, but I just can't figure out how to get it transferred.
-- Kit

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

How does your proc execute the package?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sachin4all Ten Centuries Points: 1249 More actions · Answer 2

u can create a execute sql task and create a variable to pass input value and also assign the variable to the parameter using the parameter mapping

in the execute sql task.

If your stored proc gives a result set,then assign the resultset to full result set and map the result set in the result set tab

i think this should work

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

Sachin4all (6/30/2011)
u can create a execute sql task and create a variable to pass input value and also assign the variable to the parameter using the parameter mapping
in the execute sql task.
If your stored proc gives a result set,then assign the resultset to full result set and map the result set in the result set tab
i think this should work

Your idea is for a package executing a proc.

The poster wants to execute a package from a proc.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

You would have to have a variable in the package that can use the value and then you can modify the the command that executes the package to set the variable.

which will take the form of:

/SET "\Package.Variables[User::VariableName].Properties[Value]";1

I think that will get you there..

CEWII

Kit G SSCrazy Points: 2888 More actions · Answer 5

Hi Phil,

Thanks for the reply.

Here's the procedure that's calling the stored procedure.

ALTER procedure [dbo].[ZipCountOrderID]

@OrderID_In int

as

declare @OrderID int

select @orderid = @orderid_in

EXEC xp_cmdshell 'dtexec /sql zipcodeexport'

In the SSIS package, I have a Data Flow Task which goes to a File System Task. Within the Data Flow Task I have I have the OLE DB Source which is has the query that I want to put into the file. It is this query that should have the @OrderID value but getting them to connect just ain't working.

-- Kit

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

So assuming the variable is a package variable with the name OrderId.

ALTER procedure [dbo].[ZipCountOrderID]

@OrderID_In int

as

declare @OrderID int

select @orderid = @orderid_in

declare @cmd varchar(255)

SELECT @cmd = 'dtexec /sql zipcodeexport /SET "\Package.Variables[User::OrderId].Properties[Value]";' + CONVERT( varchar(11), @orderId

EXEC xp_cmdshell @cmd

you then use the orderid variable in the package as a parameter.

Clear?

CEWII

Kit G SSCrazy Points: 2888 More actions · Answer 7

Elliott Whitlow (6/30/2011)
You would have to have a variable in the package that can use the value and then you can modify the the command that executes the package to set the variable.
which will take the form of:
/SET "\Package.Variables[User::VariableName].Properties[Value]";1
I think that will get you there..
CEWII

Hi Elliot,

Thanks for the reply. I tried adding this, but couldn't get it to work. The "not getting it to work" was definitely due to my own ignorance of what to put where and the documentation assuming I already knew how to do this and was just checking to make sure I hadn't missed anything on the syntax.

So my package name is ZipcodeExport and the variable is OrderID (Or OrderID_In)

What should the \SET ... look like when I'm done?

-- Kit

Kit G SSCrazy Points: 2888 More actions · Answer 8

Hi Phil and Elliot,

Thanks for the help. I'm now getting the Stored Proc to work. Just not getting a file so I'll have to figure out why, but that's in the SSIS package, not the SQL code.

Thanks a bunch. I definitely learned something! (which is a good thing) 🙂

-- Kit

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

The variable in the sproc is not as important, the variable in the PACKAGE is important, you have to have a place to PUT the value, the SET command will just set the value in the package, within the package you then have to use it.

CEWII