01. SSIS Configuration File - C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml ( This file configures SSIS service )
02. A solution is a container that holds a series of projects.
03. .dtsx -> An SSIS package
.ds -> A shared data source file
.sln -> A solution file that contains one or more projects
.dtproj -> An SSIS project file
.params -> A shared parameter file
.conmgr -> A shared connection manager
04. The Control Flow tab controls the execution of the package
05. The Data Flow tab handles the movement of data.
06. When Package is executed, you enter debug mode and a new tab called "Progress" immediately opens.
07. The Progress tab is where you go to debug when a package has a problem.
08. You can also go to the Output window below to see a textual view of the same Progress tab.
09. Once you stop debug mode by clicking the Stop button or by going to Debug -> Stop Debugging.
10. A package is essentially an xml file
11. Package Security is maintained in four options.
1. Encrypt sensitive with user key
2. Encrypt sensitive with password
3. Encrypt All with user key
4. Encrypt All with password
12. Microsoft encrypts your package by default with your Windows user key.
Windows User key is a key that protects your Windows user credentials on your PC.
13. A connection manager is a connection that can be leveraged and consumed once or many times in a package.
14. Project connection manager is a connection that can be leveraged from multiple packages.
15. When you upgrade packages to SSIS 2012, they are converted to use the package deployment model by default, which is the legacy way of developing packages.
16. You can invoke the SSIS Package Upgrade Wizard by
1. Simply opening your packages in SQL Server Data Tools (SSDT)
2. By manually by running "SSISUpgrade.exe"
3. By the SSIS Package Upgrade Wizard executable.
17. If you want to fully upgrade your packages to use the new deployment model, you must run the "Integration Services Project Conversion Wizard".
18. The Integration Services Project Conversion Wizard does the below:
1. Apply project-level encryption.
2. Update Execute Package Tasks to use internal project references.
3. Replace configurations with project parameters.
CONTROL FLOW
------------
Using Precedence Constraints
19. Precedence constraints are the connections between the tasks that control the execution order of each task.
A predecessor task is any task with a precedence constraint that is connected to another task.
20. The precedence constraint arrows can be different colors (Red (failure), Blue (completion) and Green (success)) to represent different commands.
They can also have an fx logo to represent an expression.
21. In the Precedence Constraint Editor, you can also set the logical AND/OR for the preceding task.
In case of multiple predecessor tasks, you can configure the task to execute
1. only if the group of predecessor tasks has completed (logical AND),
the connecting lines are solid
2. if any one of the predecessor tasks has completed (logical OR),
the lines are dotted (even if one or more predecessor tasks have failed)
22. The Precedence Constraint Editor has the below evaluation operations:
1. Constraint -> Evaluates the success, failure, or completion
of the predecessor task or tasks
2. Expression -> Evaluates the success of a customized condition
that is programmed using an expression
3. Expression and Constraint -> Evaluates both the expression and
the constraint before moving to the next task
4. Expression or Constraint -> Determines if either the expression or
the constraint has been successfully met before
moving to the next task
23. The arrows in the Data Flow tasks represents flow of good data (blue arrow) and flow of erroneous data (red arrow) to be sent to another destination separate from the good data.
24. Force Execution Value:
To create custom status information from running tasks and then use these values in precedence constraints to fine-tune the control flow
You can also return some user-defined data from a task, container, or package.
"ForceExecutionValue" set to True to turn on this feature. The default value is False.
"ForcedExecutionValueType" of the user-defined data that you intend to provide.
The default value is Int32.
"ForcedExecutionValue" - Provid a value (Static or using SSIS expression).
The default value is 0 (zero).
Manipulating Files with the File System Task
25. File System task - need to move, delete, or rename a file, or make just about any other changes to it.
1. Copy a directory
2. Copy a file
3. Create a directory
4. Delete a directory
5. Delete the contents of a directory
6. Delete a file
7. Move a directory
8. Move a file
9. Rename a file
10. Change the attributes of a file
26. The Rename file option actually performs two actions at once.
1. Renames a file
2. Moves a file if the destination is different from the source
27. The property IsSourcePathVariable enables you to use a variable for the source
28. When you are using a File System Task to set attributes of a file or folder,
you can set four attributes for a source file.
1. Hidden
2. ReadOnly
3. Archive
4. System
Coding Custom Script Tasks
29. In the Script node you have four properties to set:
1. The first is the Script Language.
Microsoft Visual Basic 2010
Microsoft Visual C# 2010
2. EntryPoint -> This is the location in the code where the task looks to execute the code
first. Generally, this is left at Main because Main is automatically set
up in the built-in starting script.
The next two properties enable you to list the variables from the package that you can use in the Script Task code:
3. ReadOnlyVariables are variables that you want to use in the Script Task code,
but you do not want the values of the variables edited.
4. ReadWriteVariables are variables used in the Script Task that can have their values
changed, meaning you can change the values of the variables to be used in the
package after the Script Task completes.
30. Expressions is a collection of expression.
The evaluation result of each expression is assigned to a property and replaces the value of the property.
Using the Execute SQL Task
31. This task is used to insert, update, select, and truncate data from SQL tables.
Any normal SQL commands you would use can be used in this task.
You can use parameters just like a stored procedure and can even call stored procedures
from the task.
The first screen on the editor lists four nodes in the left pane:
1. General - Name
, Description
, Forced Execution
, Result Set (None, Single Row, Full Result set, XML)
, SQL Statement
, ByPass Prepare(indicates whether the task should prepare the
query before executing it because prepare does not support
some Transact-SQL keywords if you are using a
parameterized query in your execute SQL task you must
insure this property is set True or the task will fail.)
, Delay Validation (- instructs to validate the task at run time).
2. Parameter Mapping - Configuring the parameters using variables
3. Result Set - Assigning the result set to the variable
The result set name is the name of the returning data.
This can be an alias you gave to a selected set of data.
If you did not give the data an alias, you would enter the number 0 to indicate the first
result set returned. The names of the parameters start at 0 and count up.
So, if you have three question marks in your SQL query, representing
three parameters, your parameter mappings will be 0, 1, and 2.
4. Expressions -
5. TimeOut is the number of seconds you want the Execute SQL Task to run before the
task stops and reports a timeout failure. A setting of zero is infinite.
6. CodePage - Code pages are set based on the code page that is used on the SQL server.
The code page option is available only for Excel, OLE DB, ADO.NET, SQL Mobile.
7. TypeConversionMode option is new in SQL Server 2012. This option allows the
Execute SQL Task to convert data types when saving to a variable.
The data types for SSIS variables do not match exactly to the data types in SQL Server.
Set this mode to Allowed and the Execute SQL Task will convert some items to match
the variable types when necessary.
8. IsQueryStoredProcedure -> This property is available on the ADO and ADO.NET options only.
This is set to True when the SQL command is calling a stored procedure from
the ADO connection.
This stored procedure name can be stored in direct input, a file connection, or a variable.
32. The three buttons at the bottom of the Execute SQL Task on the General node are:
1. Browse -> Searches for .SQL files in the filesystem
2. Build Query -> Query builder, similar to the query builder in SQL Management Studio
3. Parse Query -> Parses the SQL query checking for syntax errors
These can be used to help build the SQL query for the task.
33. DelayValidation Property is available on Task level, Connection Manager, Container and on Package level.
By default the value of this property is set to false that means that when the package start execution, it validates all the Tasks, Containers, Connection Managers and objects( Tables,Views, Stored Procedures etc.) used by them.
If any object such as table or destination file etc. is not available then Package validation fails and Package stop execution.
By setting this property to True, We enforce our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time.
34. When using the SSIS Execute Package task, one can use a Project Reference type of package for the invoked "child" package.
35. When specifying the parameter bindings, the UI lets you create a child package parameter "on the fly" (i.e. a parameter that doesnt exist in the child package).
But there are two issues with this:
1. This parameter is not available in the child after clicking OK, so while it seems you've created
a new parameter in the child package, this parameter is not available afterwards in the child
package
2. After running the parent package, the parameter binding using this "ghost" child package
parameter disappears
Using the Execute Process Task
36. The Execute Process Task enables you to call the windows or console applications.
37. Errors that occur in the outside programs can be captured in the SSIS package in a variable.
This variable value can be written to a log file or a table for auditing.
1. RequireFullFileName -> Tells the task whether it needs the full path to execute the command.
If the file is not found at the full path or in the PATH
environment variables of the machine, the task will fail.
Typically, a full path is used only if you want to explicitly identify the executable
you want to run.
However, if the file exists in the System32 directory, you wouldn’t normally have
to type the full path to the file because this path is automatically known
to a typical Windows system.
2. Executable -> Identifies the path and filename for the executable you want to run.
Be careful not to provide any parameters or optional switches in this property
that would be passed to the executable.
Use the Arguments property to set these types of options separately.
3. WorkingDirectory -> Contains the path from which the executable or command file will work.
4. StandardInputVariable -> Variable used to pass into the process as an argument.
Use this property if you want to dynamically provide a parameter to the
executable based on a variable.
5. StandardOutputVariable -> Captures the result of the execution by setting the
StandardOutputVariable property to a variable.
6. StandardErrorVariable -> Any errors that occurred from the execution are captured in the
variable you provide in this property.
7. FailTaskIfReturnCodeIsNotSuccessValue property -> Another option for validating if the
executable completed successfully.
8. SuccessValue option -> The Execute Process Task will fail if the exit code passed from
the program is different from the value provided in the SuccessValue option.
The default value of 0 indicates that the task was successful in executing the
process.
9. Timeout/TerminateProcessAfterTimeOut properties -> The Timeout property determines
the number of seconds that must elapse before the program is considered
a runaway process.
A value of 0, which is the default, means the process can run for an infinite
amount of time.
This property is used in conjunction with the TerminateProcessAfterTimeOut
property, which if set to true terminates the process after the timeout has been
exceeded.
10. WindowStyle option -> You can set the executable to be run minimized, maximized,
hidden, or normal. If this is set to any option other than hidden, users will be
able to see any windows that potentially pop up and may interact with them
during run time. Typically, these are set to hidden once a package is fully tested
and deployed to a server to be run on a schedule unattended.
Using the Expression Task *New in SSIS 2012
38. Very useful in incrementing the variables in a loop and auditing the number of files that run in a loop.
39. You can also use the Expression Task in conjunction with the precedence constraints in the Control Flow.
Using the Send Mail Task
40. The Send Mail Task sends e‑mail via Simple Mail Transfer Protocol (SMTP) from a SQL Server Integration Services (SSIS) package.
41. The Send Mail Task is most commonly used as a notification tool.
42. Can be used in Control Flow tab, Event Handler. You can place the Send Mail Task at the end of a Control Flow to send e‑mail on the successful completion of a package.
1. The event handler of a package is also a great place to place the Send Mail Task.
2. To record the run time: You can place the task in the OnPreExecute
Event Handler to notify you via e‑mail that a package has begun.
3. The OnPostExecute Event Handler can send mail at the end of a package showing the
start and end for a package, therefore enabling you to track the run time of a
package.
4. When you place a Send Mail Task in the OnError or the OnWarning Event, you can
be notified when an error or warning occurs anytime during the running of a
package.
43. A SendMail task can send attachments.
44. Other properties of the Send Mail Task are the basic fields of an e‑mail:
1. From -> The e‑mail address that will show as the sender
2. To -> The receiver of the e‑mail
3. Cc -> Sends a carbon copy e‑mail
4. Bcc -> Sends a blind carbon copy e‑mail
5. Subject -> Shows in the subject line of the e‑mail
45. The next property to set is the MessageSourceType. It has three options:
1. Direct Input -> Message is typed into the Send Mail Task
2. File Connection -> Message is saved in an external file
3. Variable -> Message is stored in a package variable
Using the FTP task
46. It enables you to send and receive files from an FTP location along with other FTP commands.
47. Note: When an FTP Task is trying to connect, the package is stopped at that point in the Control Flow.
So if the Time-out is set to a large number and has trouble connecting, the package may run for an extended period of time without actually performing any task.
48. The next option is "Use passive mode" -> If checked, this option connects the FTP server using the passive mode instead of active mode.
49. Chunk size -> This is the size of the data that is sent out in succession until the entire file is sent or received.
50. Stop on Failure -> This property stops the FTP from performing a transfer if there is a failure during the transfer process.
02. A solution is a container that holds a series of projects.
03. .dtsx -> An SSIS package
.ds -> A shared data source file
.sln -> A solution file that contains one or more projects
.dtproj -> An SSIS project file
.params -> A shared parameter file
.conmgr -> A shared connection manager
04. The Control Flow tab controls the execution of the package
05. The Data Flow tab handles the movement of data.
06. When Package is executed, you enter debug mode and a new tab called "Progress" immediately opens.
07. The Progress tab is where you go to debug when a package has a problem.
08. You can also go to the Output window below to see a textual view of the same Progress tab.
09. Once you stop debug mode by clicking the Stop button or by going to Debug -> Stop Debugging.
10. A package is essentially an xml file
11. Package Security is maintained in four options.
1. Encrypt sensitive with user key
2. Encrypt sensitive with password
3. Encrypt All with user key
4. Encrypt All with password
12. Microsoft encrypts your package by default with your Windows user key.
Windows User key is a key that protects your Windows user credentials on your PC.
13. A connection manager is a connection that can be leveraged and consumed once or many times in a package.
14. Project connection manager is a connection that can be leveraged from multiple packages.
15. When you upgrade packages to SSIS 2012, they are converted to use the package deployment model by default, which is the legacy way of developing packages.
16. You can invoke the SSIS Package Upgrade Wizard by
1. Simply opening your packages in SQL Server Data Tools (SSDT)
2. By manually by running "SSISUpgrade.exe"
3. By the SSIS Package Upgrade Wizard executable.
17. If you want to fully upgrade your packages to use the new deployment model, you must run the "Integration Services Project Conversion Wizard".
18. The Integration Services Project Conversion Wizard does the below:
1. Apply project-level encryption.
2. Update Execute Package Tasks to use internal project references.
3. Replace configurations with project parameters.
CONTROL FLOW
------------
Using Precedence Constraints
19. Precedence constraints are the connections between the tasks that control the execution order of each task.
A predecessor task is any task with a precedence constraint that is connected to another task.
20. The precedence constraint arrows can be different colors (Red (failure), Blue (completion) and Green (success)) to represent different commands.
They can also have an fx logo to represent an expression.
21. In the Precedence Constraint Editor, you can also set the logical AND/OR for the preceding task.
In case of multiple predecessor tasks, you can configure the task to execute
1. only if the group of predecessor tasks has completed (logical AND),
the connecting lines are solid
2. if any one of the predecessor tasks has completed (logical OR),
the lines are dotted (even if one or more predecessor tasks have failed)
22. The Precedence Constraint Editor has the below evaluation operations:
1. Constraint -> Evaluates the success, failure, or completion
of the predecessor task or tasks
2. Expression -> Evaluates the success of a customized condition
that is programmed using an expression
3. Expression and Constraint -> Evaluates both the expression and
the constraint before moving to the next task
4. Expression or Constraint -> Determines if either the expression or
the constraint has been successfully met before
moving to the next task
23. The arrows in the Data Flow tasks represents flow of good data (blue arrow) and flow of erroneous data (red arrow) to be sent to another destination separate from the good data.
24. Force Execution Value:
To create custom status information from running tasks and then use these values in precedence constraints to fine-tune the control flow
You can also return some user-defined data from a task, container, or package.
"ForceExecutionValue" set to True to turn on this feature. The default value is False.
"ForcedExecutionValueType" of the user-defined data that you intend to provide.
The default value is Int32.
"ForcedExecutionValue" - Provid a value (Static or using SSIS expression).
The default value is 0 (zero).
Manipulating Files with the File System Task
25. File System task - need to move, delete, or rename a file, or make just about any other changes to it.
1. Copy a directory
2. Copy a file
3. Create a directory
4. Delete a directory
5. Delete the contents of a directory
6. Delete a file
7. Move a directory
8. Move a file
9. Rename a file
10. Change the attributes of a file
26. The Rename file option actually performs two actions at once.
1. Renames a file
2. Moves a file if the destination is different from the source
27. The property IsSourcePathVariable enables you to use a variable for the source
28. When you are using a File System Task to set attributes of a file or folder,
you can set four attributes for a source file.
1. Hidden
2. ReadOnly
3. Archive
4. System
Coding Custom Script Tasks
29. In the Script node you have four properties to set:
1. The first is the Script Language.
Microsoft Visual Basic 2010
Microsoft Visual C# 2010
2. EntryPoint -> This is the location in the code where the task looks to execute the code
first. Generally, this is left at Main because Main is automatically set
up in the built-in starting script.
The next two properties enable you to list the variables from the package that you can use in the Script Task code:
3. ReadOnlyVariables are variables that you want to use in the Script Task code,
but you do not want the values of the variables edited.
4. ReadWriteVariables are variables used in the Script Task that can have their values
changed, meaning you can change the values of the variables to be used in the
package after the Script Task completes.
30. Expressions is a collection of expression.
The evaluation result of each expression is assigned to a property and replaces the value of the property.
Using the Execute SQL Task
31. This task is used to insert, update, select, and truncate data from SQL tables.
Any normal SQL commands you would use can be used in this task.
You can use parameters just like a stored procedure and can even call stored procedures
from the task.
The first screen on the editor lists four nodes in the left pane:
1. General - Name
, Description
, Forced Execution
, Result Set (None, Single Row, Full Result set, XML)
, SQL Statement
, ByPass Prepare(indicates whether the task should prepare the
query before executing it because prepare does not support
some Transact-SQL keywords if you are using a
parameterized query in your execute SQL task you must
insure this property is set True or the task will fail.)
, Delay Validation (- instructs to validate the task at run time).
2. Parameter Mapping - Configuring the parameters using variables
3. Result Set - Assigning the result set to the variable
The result set name is the name of the returning data.
This can be an alias you gave to a selected set of data.
If you did not give the data an alias, you would enter the number 0 to indicate the first
result set returned. The names of the parameters start at 0 and count up.
So, if you have three question marks in your SQL query, representing
three parameters, your parameter mappings will be 0, 1, and 2.
4. Expressions -
5. TimeOut is the number of seconds you want the Execute SQL Task to run before the
task stops and reports a timeout failure. A setting of zero is infinite.
6. CodePage - Code pages are set based on the code page that is used on the SQL server.
The code page option is available only for Excel, OLE DB, ADO.NET, SQL Mobile.
7. TypeConversionMode option is new in SQL Server 2012. This option allows the
Execute SQL Task to convert data types when saving to a variable.
The data types for SSIS variables do not match exactly to the data types in SQL Server.
Set this mode to Allowed and the Execute SQL Task will convert some items to match
the variable types when necessary.
8. IsQueryStoredProcedure -> This property is available on the ADO and ADO.NET options only.
This is set to True when the SQL command is calling a stored procedure from
the ADO connection.
This stored procedure name can be stored in direct input, a file connection, or a variable.
32. The three buttons at the bottom of the Execute SQL Task on the General node are:
1. Browse -> Searches for .SQL files in the filesystem
2. Build Query -> Query builder, similar to the query builder in SQL Management Studio
3. Parse Query -> Parses the SQL query checking for syntax errors
These can be used to help build the SQL query for the task.
33. DelayValidation Property is available on Task level, Connection Manager, Container and on Package level.
By default the value of this property is set to false that means that when the package start execution, it validates all the Tasks, Containers, Connection Managers and objects( Tables,Views, Stored Procedures etc.) used by them.
If any object such as table or destination file etc. is not available then Package validation fails and Package stop execution.
By setting this property to True, We enforce our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time.
34. When using the SSIS Execute Package task, one can use a Project Reference type of package for the invoked "child" package.
35. When specifying the parameter bindings, the UI lets you create a child package parameter "on the fly" (i.e. a parameter that doesnt exist in the child package).
But there are two issues with this:
1. This parameter is not available in the child after clicking OK, so while it seems you've created
a new parameter in the child package, this parameter is not available afterwards in the child
package
2. After running the parent package, the parameter binding using this "ghost" child package
parameter disappears
Using the Execute Process Task
36. The Execute Process Task enables you to call the windows or console applications.
37. Errors that occur in the outside programs can be captured in the SSIS package in a variable.
This variable value can be written to a log file or a table for auditing.
1. RequireFullFileName -> Tells the task whether it needs the full path to execute the command.
If the file is not found at the full path or in the PATH
environment variables of the machine, the task will fail.
Typically, a full path is used only if you want to explicitly identify the executable
you want to run.
However, if the file exists in the System32 directory, you wouldn’t normally have
to type the full path to the file because this path is automatically known
to a typical Windows system.
2. Executable -> Identifies the path and filename for the executable you want to run.
Be careful not to provide any parameters or optional switches in this property
that would be passed to the executable.
Use the Arguments property to set these types of options separately.
3. WorkingDirectory -> Contains the path from which the executable or command file will work.
4. StandardInputVariable -> Variable used to pass into the process as an argument.
Use this property if you want to dynamically provide a parameter to the
executable based on a variable.
5. StandardOutputVariable -> Captures the result of the execution by setting the
StandardOutputVariable property to a variable.
6. StandardErrorVariable -> Any errors that occurred from the execution are captured in the
variable you provide in this property.
7. FailTaskIfReturnCodeIsNotSuccessValue property -> Another option for validating if the
executable completed successfully.
8. SuccessValue option -> The Execute Process Task will fail if the exit code passed from
the program is different from the value provided in the SuccessValue option.
The default value of 0 indicates that the task was successful in executing the
process.
9. Timeout/TerminateProcessAfterTimeOut properties -> The Timeout property determines
the number of seconds that must elapse before the program is considered
a runaway process.
A value of 0, which is the default, means the process can run for an infinite
amount of time.
This property is used in conjunction with the TerminateProcessAfterTimeOut
property, which if set to true terminates the process after the timeout has been
exceeded.
10. WindowStyle option -> You can set the executable to be run minimized, maximized,
hidden, or normal. If this is set to any option other than hidden, users will be
able to see any windows that potentially pop up and may interact with them
during run time. Typically, these are set to hidden once a package is fully tested
and deployed to a server to be run on a schedule unattended.
Using the Expression Task *New in SSIS 2012
38. Very useful in incrementing the variables in a loop and auditing the number of files that run in a loop.
39. You can also use the Expression Task in conjunction with the precedence constraints in the Control Flow.
Using the Send Mail Task
40. The Send Mail Task sends e‑mail via Simple Mail Transfer Protocol (SMTP) from a SQL Server Integration Services (SSIS) package.
41. The Send Mail Task is most commonly used as a notification tool.
42. Can be used in Control Flow tab, Event Handler. You can place the Send Mail Task at the end of a Control Flow to send e‑mail on the successful completion of a package.
1. The event handler of a package is also a great place to place the Send Mail Task.
2. To record the run time: You can place the task in the OnPreExecute
Event Handler to notify you via e‑mail that a package has begun.
3. The OnPostExecute Event Handler can send mail at the end of a package showing the
start and end for a package, therefore enabling you to track the run time of a
package.
4. When you place a Send Mail Task in the OnError or the OnWarning Event, you can
be notified when an error or warning occurs anytime during the running of a
package.
43. A SendMail task can send attachments.
44. Other properties of the Send Mail Task are the basic fields of an e‑mail:
1. From -> The e‑mail address that will show as the sender
2. To -> The receiver of the e‑mail
3. Cc -> Sends a carbon copy e‑mail
4. Bcc -> Sends a blind carbon copy e‑mail
5. Subject -> Shows in the subject line of the e‑mail
45. The next property to set is the MessageSourceType. It has three options:
1. Direct Input -> Message is typed into the Send Mail Task
2. File Connection -> Message is saved in an external file
3. Variable -> Message is stored in a package variable
Using the FTP task
46. It enables you to send and receive files from an FTP location along with other FTP commands.
47. Note: When an FTP Task is trying to connect, the package is stopped at that point in the Control Flow.
So if the Time-out is set to a large number and has trouble connecting, the package may run for an extended period of time without actually performing any task.
48. The next option is "Use passive mode" -> If checked, this option connects the FTP server using the passive mode instead of active mode.
49. Chunk size -> This is the size of the data that is sent out in succession until the entire file is sent or received.
50. Stop on Failure -> This property stops the FTP from performing a transfer if there is a failure during the transfer process.
