Using Global Variables in SSIS

Question

Using Global Variables in SSIS

posh

Say Hey Kid

Points: 692
More actions
October 12, 2006 at 10:20 am

#170674

how can i use the Global Variables in SSIS... in which DTS i use to use Global variables for even passing the Server Name & Database Names.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Stephen Merkel Mr or Mrs. 500 Points: 518 More actions · Answer 1

Any variable you create in SSIS has a scope. I believe that a variable with Global scope would be one scoped at the SSIS package level as these variables are available to all child tasks,containers etc. in the package. To create a packages scoped variable, just click on an empty portion of the task pane, righ click and select variables.

posh Say Hey Kid Points: 692 More actions · Answer 2

Thanks for the suggestion.... but i have created those too but those variables i am not able to use it for passing the ServerNames, DatabaseNames & userids , Passwords to the tasks in SSIS... where as in DTS i use to use through the Global variables... If you find anything on this pls give with example so that i can test it for my dev environment.. Thanks again for the suggestion.

Stephen Merkel Mr or Mrs. 500 Points: 518 More actions · Answer 3

I believe what you are looking to do is assign the parts of your connection string dynamically. The connection string can be controlled dynamically by manipulating the properties of the connection manager.

If you highlight a connection manager and hit F4, you will see the properties of the connection manager.

Hit the elipse [...] for the expressions on the properties pane. Here you can assign variables to the various properties of the connection string ( like the pieces you mention in your question ) The property to set for 'Databasename' is 'IntialCatalog'. One thing to rember here is, I believe you must initially point the connection manager to a valid connection. At runtime, the connection manager will use the values for parts or all of he connection string you have set to be read from variables. For example you could read the names of databases from a table and iterate through them using an ADO ForEach loop. On each pass through the loop, a variable will hold the database name. By assigning this variable to the IntialCatalog property of the connection manager, the connection manager will point to (connect to) each database in turn.

As for an example: I learned a lot from this video by Brian Knight

http://www.jumpstarttv.com/Media.aspx?vid=38

posh Say Hey Kid Points: 692 More actions · Answer 4

Hi,

Thanks a lot now i am able to define and still working on something else on this.... i even registered into the site to view the video file for more examples.. Thanks again for the clarification.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Yes I can create a Global (Package Variable) but how do I write to it using a Script Task or Expressions that have a Global (Package Scope).

My problem is that I a Flat File Connection and I use an expression to create the Connection Name using Variables but I can't reference that Connection Name in the next task Send Mail when I attempt to send the file using an expression.

I need to be able find out how to write to a Package Variable,. I have no issues creating variables that are local to a task.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/