How to Create a Global Variable in SSIS
By Eric Brown
SSIS is a component of Microsoft SQL Server that was implemented with the version released in 2005. SSIS provides the ability to utilize programming code to accomplish desired tasks within SSIS. One of the programming features that SSIS offers is the ability to create and use global variables, which are variables declared in the main body of the programming source code. These variables can be utilized or called within a subroutine or across the entire program. Moreover, global variables retain stored value throughout the life cycle of the program.
Step 1
Open "Microsoft SQL Server." Select "SQL Server Business Intelligence Development Studio" on the displayed list. Click the "File" menu option. Select "New" on the displayed list. Select "Project" on the displayed list.
Step 2
Click the "Integration Services Project" option. Enter a name for the desired project. Click the "OK" button. This action will display the Package Designer window.
Step 3
Select the "SSIS" option. Choose the desired options and complete the information needed for your SSIS package.
Step 4
Click the "Variable" tab. This action will display the Variable window. Select the "Add Variable" option. Enter a name for the variable in the Variable Name field. Select "Package" as the option for the Scope field. This action will define the variable as a global variable for this particular package. Select "String" as the data type in the data type field. Enter the desired value for the variable in the Value field.
Step 5
Locate the Connection Manager in the bottom left corner, then right-click this item. This action will display the connection options. Add a new "OLE DB Connection" to the package.
Step 6
Click the "Control Flow" tab. Drag a "Script Task" to the Control Flow. Double-click the "Script Task" icon. This action will open the Script Task Editor. Select the desired options and enter the information needed for the package. Save the options and information. Close the window.
Step 7
Click the "Data Flow" tab. Add a "Data Flow Task" to the package.
Step 8
Drag an "OLE DB Source" to the Data Flow. Double-click the "OLE DB Source" icon. This action will open the OLE DB Source Editor window. Select the desired options and enter the information needed for the package. Click the "OK" button.
Step 9
Drag an "OLE DB Destination" to the Data Flow. Double-click the "OLE DB Destination" icon. This action will open the OLE DB Destination Editor window. Select the desired options and enter the information needed for the package. Click the "OK" button.
Step 10
Complete the SSIS package in the desired manner.
References
Writer Bio
Based in Augusta, Georgia, Eric Brown has been writing a variety of self-help and how-to articles since 2001. He published his first book in 2007. He holds a Master of Science in computer information systems from Georgia Southwestern University.