How to make dynamic source query with variables in SSIS?

Hi. In this article, I will show you how to make dynamic source query with variables in SSIS. Enjoyable reading.

For example, if you have a database that changes over the years and you are making an SSIS project using SQL query as a source, the variables are just for you.

I will make a simple example in SSIS (SQL Server Integration Services).

First, let’s write the source query. I have a database named ETRADE_2022. This db will be ETRADE_2023 in next year.

I have a database that changes every year. I want this query to run dynamically in my SSIS project. When we move to 2023, I want it to work according to the new database without interfering with the query.

If you are not doing an SSIS project and you want the database in your query to run dynamically, you can click here to reach my article about it.

So, Let’s create a SSIS project. After creating a project, we will assign our query to a variable.

Right-click and click Variables.

Variables window will open. Click ‘Add Variable’.

Then give the variable a name, set the data type to String and click the ellipsis.

We need to write the query in quotation marks.
To get the year dynamically in the database name, we used the following expression:
(DT_STR, <length>, <code_page>) YEAR( GETDATE ( ) ):
The length will be 4 because the year has 4 digits. Also code_page will be 1252.

By doing a type conversion, we converted the dynamic and integer year to string and integrated it into the db name.

Then, click Evaluate Expression and see the query.

As you can see, the database name is exactly what we wanted. When we move to the next year, the database name will be ETRADE_2023.

Next, we will select the variable as the source. We need to select ‘SQL command from variable’ in the data access mode section.

Next, let’s select the variable we created in the variable name section.

Then, we will see our dynamic query.

The project will continue to work dynamically.

That’s all I’m going to tell you in this article. Hope to see you in the next article.