Search code examples
sql-serverssisetlsql-server-data-toolsssis-2017

Dynamic connection string in SSIS

I am having the following problem with populating a dynamic file path for an XML file from a SSIS variable.

In Visual Studio 2017 I have an Execute SQL Task with a MS SQL stored procedure that returns two columns. The first column is a date (stored as a string) and the second is a URL. These two columns (single row) populate SSIS variables and are mapped on the Result Set tab of the Script Task. The next step is script task that uses the URL from the variable to download an xml file from a web service. The xml file is stored using a file Connection Manager. The connection string for the file is an expression that should be using the 1st variable (User::rateDate) from the Execute SQL Task

Connection String expression:

@[User::xmlFileLocation] + "ExchangeRates-" +  @[User::rateDate]  + ".xml"

This evaluates to

\server\ExchangeRates\ExchangeRates-.xml

XML file should be saved as ExchangeRates-2017-12-19.xml with 2017-12-19 being the result of the stored procedure, but instead the XML file is saved as ExchangeRates-.xml

If I manually populate the User::rateDate variable it will use that in the Connection String, but I haven't been able to get it to populate from the stored procedure result.

The date generated is part of the URL generation too so I want this both created in the same place, i.e. I don't want to assign the file name via some GETDATE() logic in the expression.

I have confirmed the variable is being populated is a Script Task C# pop up.

I have confirmed that it is not a date/string issue by changing the stored procedure result to an explicit string, like "test". It still doesn't get added to the Connection String.

Thanks, Tim

Main tab Results tab Variables 2017

Solution

  • I will provide 2 solutions based on the Stored Procedure type:

    Stored Procedure with a Select Statement

    I will assume that you are using a Stored procedure that contains a SELECT statement that return a Table of 2 columns: ServerURL and rateDate

    In this case you have to insert the result of this select statement into a temp table, then read from these temp table, as example:

    CREATE TABLE #TBL(ServerURL varchar(4000), rateDate varchar(50))

INSERT INTO #TBL EXEC pr_rateDate

SELECT TOP 1 * FROM #TBL

    In this way your variables mapping should work

    Stored Procedure with Output Parameters

    I will assume that you are using a stored procedure which require 2 output parameters to be passed, example:

    EXEC sp_rateDate @ServerURL OUTPUT, @rateDate OUTPUT

    So you have to use the following SQL statemment:

    EXEC sp_rateDate ? OUTPUT, ? OUTPUT

    and you have to add 2 output parameters in the Parameter Mapping tab

    Useful Links