2

I feel this is s simple task but I'm tripping over how to do it.

SSIS Flow

What I have is a very simple Data Flow Task that calls a stored procedure.

SELECT [Id]
      ,[Error]
  FROM [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking]

I want to update data in another location based on the Id above. I have a "Execute SQL Task" flow that calls another stored procedure with this code in it

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id

Here is how my variables are setup

Variable Screen Shot

Here is how my SQL Task is setup. I've tried Single row, full result set, anything to get it to run my update as I expect it SQL Task

And my parameter mapping SQL Task Parameters

And finally, my Result Set Result Set

Not sure what I'm doing wrong. Hoping I can get some direction

1

In the ever confusing world of SSIS supporting multiple systems, you have chosen an OLE DB Connection manager (generally a good to the only choice). The downside to using an OLE DB Connection manager with parameters, either in an Execute SQL Task or an OLE DB Source, is that parameters are ordinal based.

Execute SQL Task

In your example SQL, there are 3 parameters

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal

In your Parameter Mapping image, you need to list the User::Error twice. The Parameter Name will be 0 and 1 while User::Id has a parameter name of 2

User:Error Input NVARCHAR 0 50
User:Error Input NVARCHAR 1 50
User:Id    Input NVARCHAR 2 50

You have only provided two parameters where three are needed.

Change your result set type to None as the invocation of the above SQL does not generate a result set (or a single row of output)

Data Types

In your original parameter mapping, you indicate that the SSIS Variable User::Id should be Numeric when it lands in your OLE DB Connection but within SSIS you have it defined as a string. Unless you have a reason for data type conversions, you might be better served by keeping your types consistent. Also, is it really numeric and not int/bigint or int32/int64 in SSIS parlance.

Why doesn't the query work as is?

The query is running but no results are found WHERE SalesforceOpportunityLineItemId = @Id is testing where SalesforceOpportunityLineItemId is equal to NULL (since no value is supplied for the parameter @Id). And as we have all painfully discovered at some point in our careers, NULL will never be equal to anything.

Other thoughts

If I have misread all of that and you are trying to generate a result set from [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking] and use the Error/Id combo, then you are likely looking for a different pattern. Google "Shred Recordset SSIS" but the basics will be

Execute SQL Task (use select query, with full result set to a new Variable of type Object, call it rsUpdates)

Foreach Recordset Enumerator (use an ADO.net Recordset as the source with rsUpdates and assign values to @User::Error and @User::Id)

Execute SQL Task (use the update procedure with two variables)

Note, no data flow is required for this approach

Edit #1

A question via comment "Now that I know I need three parameters, I need to have 3 "?" behind my stored proc, correct?" I can say.. maybe

If the procedure is defined as

CREATE PROCEDURE dbo.uspU_UpdateIntegrationFlag 
(
    @error varchar(50)
,   @Id int
)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [Integration].[Staging].[OrderTracking]
    SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
    WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal
END

then ordinal based explanation above will apply to the stored procedure's parameters of @error and @id which are denoted correctly with the ?, ? and names of (0,1)

  • Thanks for this. I figured it was something simple I was overlooking. Now that I know I need three paramaters, I need to have 3 "?" behind my stored proc, correct? – jdids Dec 21 '17 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.