How to assign a value from SQL Server data to a variable in SSIS

Question

I have to extract a table from SQL Server to Excel on a weekly basis.

For this purpose I am required to include a specific ID for that week in the file name.

I have created my variable, and have created a stored procedure which returns a single value every time related to the specific week we are in.

However I am unsure how I can assign that value to the variable.

Any chance anyone can help me out?

p.s. I apologize in advance if this was already answered somewhere else, Google did not help me that much.

user9517769 · Accepted Answer · 2021-01-14 17:24:44Z

2

1. You can use the OUTPUT parameter :

When you're using the Execute SQL Task within SSIS. You must specify that the parameter is on OUTPUT and also specify it in the Parameter mappings tab.

Create the variable YourVariable in your package having the type String and map it to the result like below:

You can achieve the same result using Single Result Set : It's similar to the first step of the previous solution, except that you need to set ResultSet to SingleRow :

Then, on the Result Set tab, map the first column to the variable created in the package YourVariable :

answered Jan 14, 2021 at 17:24

user9517769

Sign up to request clarification or add additional context in comments.

1 Comment

P. MAJ Over a year ago

Awesome my friend, many thanks for the detailed walk through :)

Collectives™ on Stack Overflow

How to assign a value from SQL Server data to a variable in SSIS

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related