Creating and Using User Variables in SSIS

Question

Post reply

Creating and Using User Variables in SSIS

Phil Larkin-403636

Old Hand

Points: 336
More actions
June 2, 2008 at 9:16 am

#70501

I have an old DTS package that pulls data based on a month and year. I had Global variables that were defined with a Year(getdate) and then used that variable in the Sql Code. How do I do that in SSIS. I Know it is a simple thing but it is driving me crazy.
Phil

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 1

Hey Phil,

There is a forum where you can post your SSIS related questions.

In this case its actually really easy

a) Add a variable with a scope of package. Ensure that the variable type is datetime. For arguments sake, lets make the variable varCurrDateAndTime

b) Add an OLEDB connection in your connection manager. Getdate() will work on any database

c) Add a execute SQL task on your control flow.

Ensure that the resultset has been set equal to "single row"

Change the SQLsource type to "Direct Input"

On the SQLstatement, type in "Select getdate() as CurrentDateAndTime"

On the Resultset section, add a variable. Make the result name equal to CurrentDateAndTime, and set the variable equal to user::varCurrDateAndTime

TADA

Good luck

~PD

Phil Larkin-403636 Old Hand Points: 336 More actions · Answer 2

PD,

Thank you so much. It did work. Now I can take this veriable and use it in another Execute SQL Task? This is how my Old DTS worked.

EXEC SP_XXX,? this would plug the Global Variable into my Stored procedure. I know that SSIS is so much better but I am experiencing some painful learning curves.

Phil:D

Phil Larkin-403636 Old Hand Points: 336 More actions · Answer 3

PD,

One more question - Why wouldn't parameter mapping be used?

Phil

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 4

For sure you can, but I have to ask why?

Why not use where fieldname = getdate().

Anyways, to use the value from a variable in a different part of your SSIS package:

a) You now have a package variable called varCurrDateAndTime.

b) Add a execute SQL task. For this example, I assumed that you were not outputting anything. Therefore, change your result set to "none"

Change your SQL source to "Direct Input"

Change your SQL statement to "Exec dbo.MyProcedure @variable0 = ?, @variable1 = ?"

***NB: Every parameter that you pass is represented by a questionmark (for this example, I simply used @variable0 etc to assist with further illustration)

On the parameter mapping section, add each variable and map it to a parameter

TADA

Good luck

~PD

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 5

Phil,

Parameter mapping is only used when you are passing a variable into a stored proc.

Hope my previous posting resolves this for you

~PD

Phil Larkin-403636 Old Hand Points: 336 More actions · Answer 6

PD,

Thank you so much - That really helps. I was googling every combination I can think of but you have the answer.

Thanks again - I am going to try it.

Phil:w00t:

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 7

Hey,

happy to assist...

As indicated before, this forum is more for the database development in SQL (you will find some pretty hardcode Transact SQL here with some really amazing gurus who will explain to you the positive and negative aspects, performance and design alternatives of a specific SQL statement).

There is a SQL Server Integration Services forum which deals more with the ETL/BIDS/DTS replacement side of things.

Hopefully, you will find an answer for most of your SSIS related questions in there. Also, look at Conchango. Jamie has swallowed the SSIS book whole and has left virtually no SSIS stone unturned

~PD