June 9, 2015 at 11:39 am
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
SQLStatement: dbo.sp_ml_location_load @system_cd='03', @location_type_cd=Store;
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
Here is my set up, please let me know what is wrong here:
I Created these Variables:
Name Scope Data Type Value
system_cd Locations String '03'
location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size
User::system_cd Input NVARCHAR@system_cd -1
User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?, ?;
It runs the package successfully but it does not put the data into the table. Please help! Thanks!
June 9, 2015 at 12:07 pm
You cannot use named parameters with the OLE DB connection. The parameters in the mapping page are by ordinal starting with 0, so you need to map:
Variable Name DirectionData TypeParameter NameParameter Size
User::system_cd Input NVARCHAR0 10
User::location_type_cd Input NVARCHAR1 10
I also corrected the length of the parameters.
I'd also suggest using Extended Events or SQL Trace to see what is actually being sent to the SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 10, 2015 at 7:28 am
Thanks, I made the changes and it works.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply