SSIS - Set SQL query result to variable for Excel connection string

Question

I want to set connection string using SQL Query result from my table. I need to import excel file from certain folder every month but i need to import only latest file name (FILE_YYYYMMDD)

Here is my table

I already follow some instruction using Execute SQL Task to get the result of the query and i want to assign the result as connection string to import excel file since the folder will get new file every month.

Here is my variable and my Result Set in Execute SQL Task

I want to make FilePath with max FileDate as Excel connection string in SSIS.

Is there anyway to do that? Thanks

Answer 1

Your query can be like below :

CREATE TABLE MyTable(ID INT, FileName VARCHAR(255),FileDate DATE)

INSERT INTO MyTable(ID,FileName,FileDate) VALUES
(5,'C:\Users\Public\Documents\File_20200130.xls','2020-01-30'),
(5,'C:\Users\Public\Documents\File_20200131.xls','2020-01-31'),
(5,'C:\Users\Public\Documents\File_20220301.xls','2022-03-01')

SELECT FileName FROM MyTable
WHERE FileDate = (SELECT MAX(FileDate) FROM MyTable )

| FileName                                    |
| :------------------------------------------ |
| C:\Users\Public\Documents\File_20220301.xls |

db<>fiddle here

Create a variable for the value we need to pass, the in the Execute SQL Task component, put the query mentioned above that returns the path with the max value of the date. Please make sure to set the result set to single row.

Then go to the variables tab and add the variable there.

To store the FileName as variable and use it as connection string for Excel source :

Select the Connection Manager, and in the Properties window, you will find the Expressions entry where you can use/concatenate the variable(s) in the ConnectionString entity.