1

I have an Execute SQL Task using an Update statement that I would like to change to a stored procedure.

My stored procedure works fine in SSMS, however when I try and use the stored proc in the Execute SQL Task, the component doesn't fail, however it doesn't persist the Updated changes to the Database.

The Connection managers are solid as the Update statement works and it wouldn't even be able to call the stored proc if the Connection manager was wrong anyway.

I have tried deleting and recreating the component but that did not help. It almost seems like the query is being executed, succeeds, and then the changes are rolled back.

enter image description here

enter image description here

enter image description here

user2989759
  • 279
  • 1
  • 4
  • 14
  • 1
    Slight detour but something you should read. The sp_ prefix should be avoided. https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix – Sean Lange Oct 09 '18 at 13:09
  • Most likely it's not doing what you think it is, or the parameter values aren't what you think they are. What I would do is use SQL Profiler to capture the statement, but there is probably an easier way now with query store. PS don't start your procs with `sp_` – Nick.Mc Oct 09 '18 at 13:10
  • You can do all the debugging in the world but capturing the _actual_ query being run in Profiler will tell you pretty quickly what the issue is. The parameters aren't what you think they are, or there is something wrong with the select that you are using to test it. It's very often a good idea to add a "LastUpdated" field to a table. You might find that the values are being updated to exactly the same value. A standalone "LastUpdated" field will tell you this. – Nick.Mc Oct 10 '18 at 12:11

2 Answers2

1

I would start by checking the pstream variable type. Your stored proc expects an INT and you are passing a NUMERIC. I am guessing that the parcing doesn't work as expected. Try to have your SSIS variable 'USER::Pstream' as Int32 and the Sql task parameter type as LONG

i have tried the following and works for me.

CREATE TABLE dbo.TestTable(
    [BucketName] NVARCHAR(250),
    [DBName] NVARCHAR(250),
    [Pstream] INT
)

GO 

CREATE PROCEDURE dbo.UpdateBucket
    @BucketName NVARCHAR(250),
    @DbName NVARCHAR(250),
    @PStream INT
AS
BEGIN

    UPDATE dbo.TestTable
    SET BucketName = @BucketName
    where DBName = @DbName and Pstream = @PStream

END

GO

INSERT INTO dbo.TestTable VALUES ('A bucket', 'A database', 100)

enter image description here enter image description here

enter image description here

SNicolaou
  • 550
  • 1
  • 3
  • 15
  • Yes I have tried this because I originally had it set to LONG. I am beginning to think that this is a datatype issue of some sort. – user2989759 Oct 09 '18 at 13:49
  • can you provide the ssis data type of the other variables that are passed on? – SNicolaou Oct 09 '18 at 13:58
  • The datatype of the variables in SSIS is as follows: bucketname - VARCHAR, DBName - VARCHAR, PStream - Int32. – user2989759 Oct 09 '18 at 14:02
  • i have edited my answer. the ssis variables bucketname and dbname are of type String and pstream is Int32 – SNicolaou Oct 09 '18 at 14:25
  • Sorry, when I said that bucketname and DBName were VARCHAR I meant to say String. My solution seems exactly the same as what you have. Can you share your general tab on the component editor to see if there is any difference there please? – user2989759 Oct 09 '18 at 14:45
0

Add a breakpoint before the task executes so that you can inspect the variables before it executes. Once your package stops at the breakpoint, use the Watch window to see what the current values are that will be used in your stored proc. I suspect that you will find they are different than expected. If you have never used Watch, this should get you going:

Watch variables during SSIS Debug

Jeremy J.
  • 697
  • 4
  • 9