Using Variables - SSIS beginner 1

[Spidy6123]

Hi,
I'm reading the help files included in the SQL Server which have proven to confuse me more than actually help... :/

So I was hoping that you guys could help me with something that should be very basic.. Package scoped variables and using them in Execute SQL Tasks.

Here's what I have under my variables:
Name - varYEARMONTH
Scope - Package
Value - '200704'

under the Execute SQL task I have the following configured:
Parameter Mapping:
Variable Name - varYEARMONTH
Direction - Input
Data Type - Long
Parameter Name - YEARMONTH


Now I don't understand how I am supposed to actually use the variable within the SQL statement..

What I would like to do, is use the variable as a criteria...

Any help is appreciated..

 

[MDXer]

If you are trying to use your variable in an Execute SQL task then you simply denote the variable with a ? as for the parameter mapping don't use the Variable name but use it's ordinal in the statement. so if you are only passing 1 variable the Parameter name would be 0.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Spidy6123]

Great! Thanks, that certainly made things a little easier..

One more question: how about using these parameters within a data transfer task? I have some specific Sql queries within the data transfer task and I would like to apply a dynamic parameter for criteria..

is this possible?

TIA

 

[MDXer]

Use a derived column task. You can have this create a new column or use logic and have it replace values in a column.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Spidy6123]

I see that variables are available via the derived column task.. I'm just not sure how to put this together.

Within my Data Transfer task I have 3 tasks:
1. OLE DB Source (Which contains the SQL for the dataset where I would like to apply a parameter criteria)
2. Export Column Task
3. OLE DB Destination

Are there any samples out there that you might know of.. as I mentioned, I'm just not familiar with the usage of a derived column task?

 

[MDXer]

hopefully this will get you started
How to: Derive Column Values Using the Derived Column Transformation

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Spidy6123]

Thanks.. I think I understand how the derived column task works..

but I don't see how this will apply criteria to the original OLE DB Source query...

 

[MDXer]

can you elaborate on what it is exactly you are trying to do?


Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Spidy6123]

well .. basically I have a fairly large SSIS package which pulls data from multiple tables from a servers I only have read access to..

The data on these tables hold a Year_month field which I use as my main criteria.. I would like to be able to use a global user variable in order to define which Year_Month I will be pulling from this read only server. I do this pull monthly and it's not very practical to go through all of my data transfer tasks + execute sql tasks and manually change the Year_month criteria in each one..

I would love to be able to just modifiy a user variable and launch the package..

Your first response solved this problem for all of the Execute SQL Tasks in the package.. I'm only having problems with the Data Transfer Task: OLE DB Source: Query.. I can't seem to pull the user variable and use it in this query as a criteria.

 

[Spidy6123]

I have just discovered that the usage is the same
it's a problem with one of my queries...

Thanks for all of your help!!!

 

[RiverGuy]

On your source query, you can choose SQL command from variable under Data access mode and set the SQL statement equal to a string variable which you have created. This is if you use a Data Flow Task. Your string would be built before the execution of your data flow task and be built with elements of your other variables.

 

[Spidy6123]

this is frustrating!!

the data transfer task is working correctly.. but now my more complicated Execute SQL tasks are failing..


Here is an example of a query:

update profile_tbl
set industry = b.industry
from profile_tbl a inner join my_dimension b
on a.my_code = b.my_code
where year_month = ?


My parameter mapping is as fallows:
Direction: Input
Data Type: Varchar
Parameter name: 0


this works fine if I replace the ? with a static value..
Any ideas?

BTW I really appreciate all of your help guys..

 

[Spidy6123]

nevermind.. I figured it out..

for each ? I need to do a parameter mapping even though their the same variable..

thanks!

 

[Spidy6123]

another question.. is it possible to do the following..
I can't seem to get it working.. but maybe Im doing something wrong?

I would like to have one parameter configured and use it throughout the query:

DECLARE @MyYEARMONTH long
SET @MyYEARMONTH = ?

and then just use @MyYEARMONTH throughout the rest of the query....

 

[AlexCuse]

Where are you trying to use this?

I think that local variables are allowed in an execute SQL Task, not sure about the source query of a Data Flow.

Anyway, you will have trouble finding the 'long' data type in SQL Server.

Try 'int' instead.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom