Create SSIS Package - SQL Server

To create a new SSIS package, follow these steps:
1. Launch Business Intelligence Development Studio

2. Select File -> New -> Project.
3. Select the Business Intelligence Projects project type.

4. Select the Integration Services Project template.

5. Select a convenient location.

6. Name the new project ISProject1 and click OK.

Working with Connection Managers

SSIS uses connection managers to integrate different data sources into packages.
SSIS includes a wide variety of different connection managers that allow you to move data around from place to place. Table 16-1 lists the available connection managers.

ADO Connection Manager Connecting to ADO objects such as a Recordset
ADO.NET Connection Manager  - Connecting to data sources through an ADO.NET provider.
Analysis Services Connection Manager  - Connecting to an Analysis Services database or cube.
Excel Connection Manager  - Connecting to an Excel worksheet.
File Connection Manager - Connecting to a file or folder.
Flat File Connection Manager  - Connecting to delimited or fixed width flat files.
FTP Connection Manager - Connecting to an FTP data source.
HTTP Connection Manager Connecting to an HTTP data source.

MSMQ Connection Manager Connecting to a Microsoft Message Queue.
Multiple Files Connection Manager Connecting to a set of files, such as all text files on a particular hard drive.
Multiple Flat Files Connection Manager Connecting to a set of flat files.
ODBC Connection Manager Connecting to an ODBC data source.
OLE DB Connection Manager Connecting to an OLE DB data source.
SMO Connection Manager Connecting to a server via SMO.
SMTP Connection Manager Connecting to a Simple Mail Transfer Protocol server.
SQL Server Mobile Connection Manager Connecting to a SQL Server Mobile database.
WMI Connection Manager Connecting to Windows Management

To create a Connection Manager, you right-click anywhere in the Connection Managers area of a package in BIDS and choose the appropriate shortcut from the shortcut menu. Each Connection Manager has its own custom configuration dialog  box with specific options that you need to fill out.

Try It!


To add some connection managers to your package, follow these steps:
1. Right-click in the Connection Managers area of your new package and select New OLE DB Connection.
2. Note that the configuration dialog box will show the data connections that you created in Chapter 15; data connections are shared across Analysis Services and Integration Services projects. Click New to create a new data connection.
3. In the Connection Manager dialog box, select the SQL Native Client provider.
4. Select your test server and provide login information.
5. Select the Chapter16 database.
6. Click OK.
7. In the Configure OLE DB Connection Manager dialog box, click OK.
8. Right-click in the Connection Managers area of your new package and select New Flat File Connection.
9. Enter DepartmentList as the Connection Manager Name.
10. Enter C:\Departments.txt as the File Name (this file will be supplied by your instructor).
11. Check the Column Names in the First Data Row checkbox. Figure 16-4 shows the completed General page of the dialog box.



 
12. Click the Advanced icon to move to the Advanced page of the dialog box.
13. Click the New button.
14. Change the Name of the new column to DepartmentName.
15. Click OK.
16. Right-click the DepartmentList Connection Manager and select Copy.
17. Right-click in the Connection Managers area and select Paste.
18. Click on the new DepartmentList 1 connection to select it.


Use the Properties Window to change properties of the new connection.
Change the Name property to DepartmentListBackup. Change the ConnectionString property to C:\DepartmentsBackup.txt.

The Control Flow tab of the Package Designer is where you tell SSIS what them package will do. You create your control flow by dragging and dropping items from the toolbox to the surface, and then dragging and dropping connections between the objects. The objects you can drop here break up into four different groups:

• Tasks are things that SSIS can do, such as execute SQL statements or transfer objects from one SQL Server to another. Table 16-2 lists the available tasks.
• Maintenance Plan tasks are a special group of tasks that handle jobs such as checking database integrity and rebuilding indexes. Table 16-3 lists the maintenance plan tasks.
The Data Flow Task is a general purpose task for ETL (extract, transform, and load) operations on data. There’s a separate design tab for building the details of a Data Flow Task.
• Containers are objects that can hold a group of tasks. Table 16-4 lists the available containers.


To add control flows to the package you’ve been building, follow these steps:


1. If the Toolbox isn’t visible already, hover your mouse over the Toolbox tab until it slides out from the side of the BIDS window. Use the pushpin button in the Toolbox title bar to keep the Toolbox visible.
2. Make sure the Control Flow tab is selected in the Package Designer.
3. Drag a File System Task from the Toolbox and drop it on the Package Designer.
4. Drag a Data Flow Task from the Toolbox and drop it on the Package Designer, somewhere below the File System task.
5. Click on the File System Task on the Package Designer to select it.
6. Drag the green arrow from the bottom of the File System Task and drop it on top of the Data Flow Task. This tells SSIS the order of tasks when the File System Task succeeds.
7. Double-click the connection between the two tasks to open the Precedence Constraint Editor.
8. Change the Value from Success to Completion, because you want the Data Flow Task to execute whether the File System Task succeeds or not.
9. Click OK.
10. Select the File System task in the designer. Use the Properties Window to set properties of the File System Task. Set the Source property to DepartmentList. Set the Destination property to DepartmentListBackup.Set the OverwriteDestinationFile property to True.

Building Data Flows

The Data Flow tab of the Package Designer is where you specify the details of any Data Flow tasks that you’ve added on the Control Flow tab. Data Flows are made up of various objects that you drag and drop from the Toolbox:
• Data Flow Sources are ways that data gets into the system. Table 16-5 lists the available data flow sources.
• Data Flow Transformations let you alter and manipulate the data in various ways. Table 16-6 lists the available data flow transformations.
• Data Flow Destinations are the places that you can send the transformed data.

Try It!


To customize the data flow task in the package you’re building, follow these steps:

1. Select the Data Flow tab in the Package Designer. The single Data Flow Task in the package will automatically be selected in the combo box.
2. Drag an OLE DB Source from the Toolbox and drop it on the Package Designer.
3. Drag a Character Map Transformation from the Toolbox and drop it on the Package Designer.
4. Drag a Flat File Destination from the Toolbox and drop it on the Package Designer.
5. Click on the OLE DB Source on the Package Designer to select it.
6. Drag the green arrow from the bottom of the OLE DB Source and drop it on top of the Character Map Transformation.
7. Click on the Character Map Transformation on the Package Designer to select it.
8. Drag the green arrow from the bottom of the Character Map Transformation and drop it on top of the Flat File Destination.
9. Double-click the OLE DB Source to open the OLE DB Source Editor.
10. Select the HumanResources.Department view. Figure 16-7 shows the completed OLE DB Source Editor.

11. Click OK.

12. Double-click the Character Map Transformation.
13. Check the Name column.
14. Select In-Place Change in the Destination column.
15. Select the Uppercase operation. Figure 16-8 shows the completed Character Map Transformation Editor.

16. Click OK.

17. Double-click the Flat File Destination.
18. Select the DepartmentList Flat File Connection Manager.
19. Select the Mappings page of the dialog box.
20. Drag the Name column from the Available Input Columns list and drop it on top of the DepartmentName column in the Available Destination Columns list.

The data flows in this package take a table from the Chapter16 database, transform one of the columns in that table to all uppercase characters, and then write that transformed column out to a flat file.