SSIS variables allow you to store values for use during package execution. Variables can be used to update package properties during runtime, store integer values used for controlling container looping, provide lookup information, store values for use within a T-SQL statement or Script task, and build expressions. Figure shows the Variables window.
A variable is defined by its namespace (either system or user-defined). You can create new user defined variables, but you cannot create new system variables.
A variable is also defined by its scope, variable datatype, and value. Scope defines where the variable can be seen from within the package. A variable with package-level scope can be viewed by all objects within the package, whereas variables defined within a task’s scope can be viewed only by the task. Allowed variable datatypes include Boolean, byte, char, datetime, DBNull, double, int16, int32, object, sbyte, single, string, and Uint32.
To configure variables within your SSIS package, follow these steps:
Now that you know how to configure variables, let’s run through creating a variable for a business scenario.
Say that your company’s HR department has an application that uses the AdventureWorks table [HumanResources].[JobCandidate] to track job candidates in the queue. Once a week, the HR group wishes to evaluate the number of candidates in the queue, to determine whether or not to pull the job advertising campaign from the local newspaper.
In this example, you will create a variable called JobCandidateCount, populate it from the table, and e-mail an advertising update to HR based on the variable value.
This expression evaluates your user variable, testing to TRUE if the variable is less than 11. Click OK. Notice that a small function symbol appears by the Retain or Start Advertising task.
Since the candidate count is more than ten, you’ll see the Execute SQL task turn green, and then the Pull Advertising task will turn green afterward. The Retain or Start Advertising task is not run, as the variable value was not less than ten.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.