Creating a Basic Package (SQL Server Video)
Applies to:Microsoft SQL Server Integration Services |
Authors: Carla Sabotta, Microsoft Corporation |
Length: 00:07:55 Size: 12.2 Mb Type: WMV file |
Watch this Video on TechNet, to watch, share, and download the video in multiple formats. |
|
Video Summary
This video demonstrates how to create a basic package by using the SSIS Designer in Business Intelligence Development Studio.
Video Transcript
Hello, my name is Carla Sabotta. I write documentation for the Microsoft SQL Server Integration Services product.
In this video, I’m going to show you how to create a basic package by using the SSIS Designer in BI Development Studio.
You’ll learn how to create a basic Integration Services package by creating the project for the package, adding a data flow task to the control flow of the package, and adding elements to the data flow.
A package is a collection of control flow and data flow elements that runs as a unit. The package must include at least one control flow element and can include one or more data flow elements.
The first step in creating a package is to create a project. The project is based on the Integration Services project template. This template creates a new project that contains a single package.
You have the option of modifying the project name, which we’ll change to Integration Services Basic Package. We’ll accept the default location and the other default settings.
Because the package must have at least one control flow element, we’ll add a task to the Control Flow tab.
In this demonstration, we’ll add a Data Flow task. A data flow task is used to extract, modify, and load data.
The annotation that we’ve added to the data flow task briefly documents what the task does. Annotations make it easier to understand and maintain a package.
Next, we’ll add elements to the data flow. A Data Flow task requires a source and destination component, and typically also includes one or more transformation components.
In this demonstration, we’ll add an OLE DB Source, an OLE DB Destination, and a Data Conversion transformation. We’ll connect the output of the source component to the input of the transformation, and the output of the transformation to the input of the destination. Constructed in this way, the data flow will extract data from the source component, convert the data type of selected columns, and load the modified data into the destination component.
Source components and destination components use connection managers to connect to data sources.
In this demonstration, we’ll create a single OLE DB connection manager for both the source and destination that connects to the AdventureWorks database.
The next step is to configure the source, transformation, and destination components.
We’ll configure the OLE DB source component as follows:
Confirm that the connection manager we created is selected.
Select the source from which the component extracts data. In this demonstration, we’ll select the Production.Product table.
Select the columns to include in the source.
In this demonstration, we’ll clear all columns in the Available External Columns table, and then select the following columns to include in the source: Name, ListPrice, Size, Weight, and SellStartDate.
We’ll configure the Data Conversion transformation as follows:
Select the columns to convert, in the Available Input Columns table. These input columns are from the source component that is connected to the transformation.
In this demonstration, we’ll convert the SellStartDate column by changing the data type from the DT_DBTIMESTAMP data type, which stores date and time data, to the DT_DBDate data type, which stores only date data.
We’ll configure the OLE DB Destination as follows:
Confirm that the connection manager we created is selected.
Select the source into which the component loads the modified data. In this demonstration, we’ll create a new table.
Integration Services generates a default CREATE TABLE statement based on the connected data source. You have the option of modifying the CREATE TABLE statement.
In this demonstration, we’ll change the table name to OLE DB Destination_BasicPackageTutorial, remove the OLE DB Source.SellStartDate column and keep the version of the column modified by the Data Conversion transformation (DataConversion.SellStartDate).
Confirm that the input columns are mapped correctly to the output columns
Finally, we’ll add a data viewer to view the modified data that is loaded into the destination.
Now, we’re ready to run the package.
As you can see, a SellStartDate column that only contains date data is loaded into the destination component.
This video demonstrated how to create a basic Integration Services package by using the SSIS Designer in BI Development Studio. The video demonstrated how to create a project, and how to add a control flow and data flow to the package.
Thank you for watching this video. We hope that you have found this of value, and will return to the Web site to view other Microsoft SQL Server Videos.