Dynamically Store SQL Query Results into SSIS Variables

Learn how to effectively store SQL query results into SSIS variables without using multiple Execute SQL Tasks in SSIS 2019.
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Dynamically store values from a sql query into SSIS variables

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Dynamically Store SQL Query Results into SSIS Variables

When working with SQL Server Integration Services (SSIS), a common challenge is the requirement to dynamically store results from SQL queries into SSIS variables. Particularly, in SSIS 2019, many users seek efficient methods for setting initial values in multiple variables without employing numerous Execute SQL Tasks. In this blog, we will explore a solution to this problem in a clear and structured manner.

Understanding the Challenge

You might be seeking to store values returned from a SQL query directly into a specific SSIS variable. For example, if you want the maximum date from a date column to be stored in an SSIS variable called [User::MaxDate], the traditional way of using expressions might not work.

Problem Illustration

Variable: [User::MaxDate]

Expression: (SELECT MAX(dateCol) AS dt FROM tblDate)

In this scenario, the expression would not evaluate the query itself but rather store the expression text, which isn't helpful for your needs.

A Solution to Dynamically Store Values

Fortunately, there is a better way to achieve this without the need for multiple Execute SQL Tasks. Below is a breakdown of how you can store SQL query results effectively.

Single Execute SQL Task for Multiple Variables

To streamline the initialization of multiple variables, you can retrieve the necessary data in a single SQL query. This method enables you to use just one Execute SQL Task instead of ten.

Preparing Your SQL Query

If the data you need to store is spread across multiple columns within a single table, you can construct a query like:

[[See Video to Reveal this Text or Code Snippet]]

Alternatively, if you need to gather data from separate tables, you can still construct a "single" query like this:

[[See Video to Reveal this Text or Code Snippet]]

This will return a single row containing multiple columns of data that you can map to your SSIS variables.

Configuring the Execute SQL Task

Once you have your SQL query ready, proceed with the following steps in your SSIS package:

Drag an Execute SQL Task onto your control flow.

Set the SQL Statement to your constructed query.

Change the Result Set property to Single row.

Navigate to the Results Tab and map the SQL query result to your SSIS variables.

Mapping Variables

To map your SQL results to SSIS variables effectively, follow these guidelines:

For OLE DB: Use zero-based ordinal indexing.

For ODBC: Use one-based ordinal indexing.

For ADO.NET: Utilize named entities.

An example mapping might look like this if using an OLE DB provider:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By adopting this streamlined approach to use a single Execute SQL Task, you can efficiently set initial values for multiple variables in your SSIS package without resorting to cumbersome multiple tasks or complex configurations.

This method not only makes your package cleaner and more manageable but also enhances performance by reducing overhead. Implement these techniques to enhance your SSIS workflows today!