SSIS Variables

[dbomrrsm]

Very new to SSIS so please excuse my ignorance.

I have managed to populate variables with the results of a script - example:

declare @var1 int
set @var1 = 1
Select @var1

However, what I need to know is if it is possible to populate a package and/or task variable with the value of @var1 without the Select at the end of the code.

I only ask as some scripts we need to run use masses of variables and I dont really want to have to select them all at the end of the script.

TIA

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[IBACFII]

Are you going to set the package level variable in a script task? If so you just need to add the variables to the proper tab on the script task. ie User::Var1, User::Var2
This setting of variables and expressions gave me many headaches as I started using SSIS but after using them a few times it got much easier. I also used a book called SQL Server 2005 Integration Services. I think it was by WROX.

 

[dbomrrsm]

IBACFII

Thanks for the reply but I have managed to do what you suggest above but only by including the select at the end of the script - Select @var1 - can package variables be assigned without the select ?

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[MDXer]

package variables can be set a Number of ways. Sucha as a script Task, Execute SQL task or even through dynamic configurations. which method is best depends mainly what the variable is, how often does it change, and the purpose.

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

 

[IBACFII]

I think that I know what you want. Have you used a "Execute SQL task?" This task allows SSIS to return a result set from a query and then you can set your variable(s) in the resultset tab. I use this for a listing of ID's that are to be processed and then "shred" it into a for each loop.

 

[dbomrrsm]

I am aware that variables can be set in a number of ways but I want to know (using an SQL Task as in my original post) can a package variable be set using the sql quoted without the

Select @var1

ie can the below assignment of a variable be mapped to a package or task variable:

declare @var1 int
set @var1 = 1

My understanding is that the above creates a variable @var1 and sets it to a value of 1 - it now exists - can this variable now be used to set a package/task variable to have the same value WITHOUT the use of the Select @var1

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[IBACFII]

Can you post you the SQL. If it is the above you could either. If you are trying to declare the variable in the SQL Statement and then use that declared variable you might be out of luck.

If you use a "script task" then
Dts.Variables.Item("User::Var").Value = 1

If you use a going to statically assign the var then
use the variable manager

I don't see why you would declare a variable this way and not either use the above methods or if the variable is a result of a query then using the resultset part of the sql task.