SSIS - Output a single value from an OLE DB Command and use it on a OLE DB Source

Question

I have a data flow working just fine, it is compound of a source that is evaluated by a lookup component and then it does an upsert, diagram is shown here:

Now, on BanqueCIBI (the ole db source), I have a SQL Command Text where I would like to receive a param from another component to use it as valueDate. This is the query right now:

SELECT [IdTransactionType]
  ,[IdBank]
  ,[IdBanqueDetailHistoryRef]
  ,[IdBanqueDetail]
  ,[IdBanqueHeader]
  ,[CCI]
  ,[ValueDate]
  ,[Text]
  ,[Reference]
  ,[Amount]
  ,[Sign]
  ,[IdCurrency]
  ,[OrigBranch]
  ,[dtCreatedOrModified]
  ,[oldText]
  ,[oldReference]
  ,[IdAccount]
  ,[IdSubAccount]
  ,[Date]
  ,[IdRD]
  ,[Flag]
  ,[History]
  ,[DtDate]
  ,[iTIB]
  ,[iSAP] 
FROM [dbCibi3].[dbo].[BanqueDetailHistoryRef] 
WHERE [ValueDate] = '2015-31-01'

So, the diagram would look something like this:

Right now, that new OLE DB Command looks like this:

And this is the usp_GetDateParamsSSIS invoked in the source above:

USE [dbMODIFE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_GetDateParamSSIS] 
    @name VARCHAR(50) = NULL,
    @value DATETIME OUTPUT
AS
BEGIN
    SELECT TOP 1 @value =valueDate FROM helperPARAMS_SSIS WHERE name = @name;
END

So, how could I use that @value OUTPUT on the BanqueCIBI component? Thank you so much! (Please notice that BanqueCibi and the new component are querying different servers and a linked served is not an option because of company's policies).

Where is the Name parameter that you supply to the stored proc coming from? — Tab Alleman, Jul 24 '15 at 17:44
I'm not sure if I catch your comment right. But it should come from the output of the stored proc I've just added. — Multitut, Jul 24 '15 at 17:48
Err, I only see one stored proc in your question. It takes Name as an input and returns Value as an Output. So my question, where are you getting the value of Name that you are passing IN to the stored proc when you call it? — Tab Alleman, Jul 24 '15 at 17:53

score 3 · Accepted Answer · answered Jul 24 '15 at 17:59

Ok, since you are passing a hard-coded Name parameter to your stored procedure, I am assuming that this is a stored procedure you only need to call once for each execution of the package, and not something you're calling once for every row in your data source.

In that case, do NOT call the stored proc with an OLE DB Command in the data flow.

Instead, call it with an Execute SQL Task that you put BEFORE the DataFlow Task in the Command Flow. Direct the return value of the proc to a package-level variable.

Then in the Source of your dataflow (BanqueCIBI), map that variable to the first parameter of your SELECT query.

There are examples of all of these techniques easily available on the internet. But if you find one you are having trouble following, feel free to edit your question with the details, or create a new question if it is sufficiently different in scope from this one.

SSIS - Output a single value from an OLE DB Command and use it on a OLE DB Source

1 Answers1