Search code examples
sql-serverssisoledb

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

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: enter image description 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: enter image description here

Right now, that new OLE DB Command looks like this: enter image description here

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).

Solution

  • 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.