SSIS - Execute SQL Task - Execute Stored Procedure With Parameters Does Not Work

• jruth 12707

  Mr or Mrs. 500

  Points: 563

  More actions

  June 9, 2015 at 11:39 am

  #301388

  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!

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  June 9, 2015 at 12:07 pm

  #1804071

  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

• jruth 12707

  Mr or Mrs. 500

  Points: 563

  More actions

  June 10, 2015 at 7:28 am

  #1804332

  Thanks, I made the changes and it works.