Get Full Access for Only $49.00
[tabby title=”Course Overview”]
In 2005 Microsoft introduced SQL Server Integration Services, SSIS, as their primary ETL (Extract, Transform, and Load) tool. SSIS fully integrates with the Common Language Runtime, CLR, and provides a platform for building enterprise-level data integration and data transformations solutions. Integration Services includes a number of built-in tasks and transformations as well as tools for constructing packages and the service for running and managing packages. You can use SQL Server Data Tools, SSDT, to create solutions without writing a single line of code; or you can write managed code using the Integration Services object model to create packages programmatically or code custom tasks and other package objects.
[tabby title=”Sessions”]
Session 1 – Creating dynamic packages –
In a perfect world an ETL solution would only have to import data from a single file that always had the same name deliver it to a single already existing table. Unfortunately this case is the exception rather than the rule. How can you use SSIS to:
1. Iterate through all files in a folder and import their content into SQL
2. Dynamically create and name table based on a data source
3. Dynamically create a T-SQL statement at runtime based on run time conditions
This session focuses on how to create SSIS packages that can change to meet runtime events dynamically based on the environment.
Each attendee will leave with the knowledge and toolset necessary to:
1. Use expressions within SSIS packages
2. Use variables to dynamically create SQL statements
3. Configure custom dynamic logging
4. Use package configurations and parameters and environments to make packages more portable at execution
Session 2 – Error handling and troubleshooting –
In a perfect world an ETL solution would only have to import data from a single file that always had the same name deliver it to a single already existing table. Unfortunately this case is the exception rather than the rule. How can you use SSIS to:
Each attendee will leave with the knowledge and toolset necessary to:
1. Use expressions within SSIS packages
2. Use variables to dynamically create SQL statements
3. Configure custom dynamic logging
4. Use package configurations and parameters and environments to make packages more portable at execution
Session 03 – Deployment models –
After you complete development of your SSIS solution you must decide on how your project/packages will be stored. Since SSIS was first introduced we have always had the ability to deploy packages to the msdb database, the file store, or the file system. SSIS 2012 introduces a new deployment option that allows us to deploy an entire project to the SSIS catalog. This session will take an in depth look at each deployment option and discuss what considerations should be taken before choosing a deployment.
Each attendee will leave with the knowledge and toolset necessary to:
1. Describe the difference between each deployment
2. Know the steps necessary to deploy packages and projects in each deployment option
3. View the reports within the SSIS catalog
Session 04 – Replacing dynamic SQL with variables and expressions in SSIS –
Dynamic SQL provides a means of defining a query at runtime which provides a way to dynamically generate a query based on runtime factors. Unfortunately this technique can come at a cost by opening up the possibility for SQL injection attacks and performance considerations. This session looks at replacing dynamic SQL within an SSIS package using several different techniques.
Each attendee will leave with the knowledge and toolset necessary to:
1. Use a variable to define the initial catalog in a connection manager
2. Use a variable to execute a query
3. Use a variable and expressions to create a query
Session 05 – Leveraging the Execute SQL task in SSIS –
The execute SQL task in an SSIS package provides a very powerful tool in an ETL solution that can be used for much more than just executing a DDL or DML statement. This session focuses on the advanced techniques that can be used for an execute SQL task to make packages more versatile and powerful.
Each attendee will leave with the knowledge and toolset necessary to:
1. Execute a query in an execute SQL task
2. Execute a stored procedure with both input and output parameters
3. Use an execute SQL task to populate a variable with an entire result set
[tabby title=”Speaker”]
David D
ye’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.
[tabbyending]