Lesson 11

This module disucssed how to load and export data a number of different ways in SQL Server
By now, you should be able to:

1. Load data with the insert statement
2. Load data with the select into statement
3. Use the bulk insert statement
4. Use the bcp program
5. Identify key features of Data Transformation Services

SQL Server Data Tools (SSDT) in Visual Studio allows you to create SQL Server Integration Services (SSIS) packages that can be scheduled to run automatically. Here's a step-by-step guide:

1. Install SSDT:
   • If not already installed, download SSDT for Visual Studio from Microsoft's website
   • Select "SQL Server Integration Services" during installation
2. Create a new Integration Services Project:
   • Open Visual Studio
   • Go to File → New → Project
   • Select "Integration Services Project" under Business Intelligence templates
3. Design your package:
   • In Solution Explorer, double-click Package.dtsx to open the designer
   • Use the Control Flow tab to add tasks (Execute SQL, Data Flow, etc.)
   • Use the Data Flow tab to design ETL processes (sources, transformations, destinations)
   • Configure connection managers for your data sources/destinations
4. Test your package:
   • Click the Start Debugging button (or F5) to test your package in Visual Studio
   • Verify all components execute successfully

Option 1: Deploy to SSIS Catalog (Project Deployment Model)

1. Configure project properties:
   • Right-click project → Properties
   • Set Configuration to "Development"
   • Under Deployment, set Server name to your SQL Server instance
2. Build and deploy:
   • Right-click project → Build
   • Right-click project → Deploy
   • Follow the deployment wizard to deploy to SSISDB catalog
3. Schedule in SQL Server Agent:
   • Open SQL Server Management Studio (SSMS)
   • Connect to your SQL Server
   • Expand SQL Server Agent → Jobs
   • Right-click → New Job
   • Add a new step of type "SQL Server Integration Services Package"
   • Select package source as "SSIS Catalog"
   • Browse to your deployed package in SSISDB
   • Configure the schedule on the Schedules page

1. Build package:
   • Right-click project → Build
   • The .dtsx file will be created in the bin folder
2. Deploy package:
   • Copy the .dtsx file to your target server
   • You can store it in the file system or MSDB database
3. Schedule with SQL Server Agent:
   • Create a new SQL Server Agent job
   • Add a step of type "SQL Server Integration Services Package"
   • Select package source as "File system" or "SQL Server" (for MSDB)
   • Browse to your package location
   • Configure the schedule

Additional Tips

• For complex scheduling, consider using the built-in SSISDB catalog features like environments, parameters, and logging
• Test your scheduled job with a manual run before relying on the schedule
• Monitor job execution history in SQL Server Agent
• Consider error handling and notifications in your package design

The following terms were introduced to you in this module:

1. COM: Component Object Model is a model for APIs used to access data.
2. Compute by: A TSQL extension that allows you to have summary rows intermixed with detail rows.
3. Collation sequence: Determines the order in which unicode data is sorted.
4. (DTS):Data transformation service A SQL Server component that allows you to transfer data between multiple data sources.
5. Derived columns: A calculated column. SQL Server allows you to define derived columns as part of the table definition.
6. Identity column: An automatically incrementing column.
7. Microsoft Repository: A set of Microsoft ActiveX interfaces and data models that are used to define database schema and data transformations as specified by the Microsoft Data Warehousing Framework.
8. msdb: The system database that is used to store SQL Server Agent information and DTS information.
9. Package: A set of DTS commands used to transform data
10. SQL Server Agent: A SQL Sever component that allows you to schedule jobs, define alerts, and define operators.
11. Task: One of the steps in a job.
12. Workflow: Describes the steps need top be done to complete a task.

The next module will show you how to monitor SQL Server using SQL Server Management Studio (SSMS).

Click the Quiz link below to see how well you've mastered the concepts in this module.
Load SQL Data - Quiz