This video demonstrates how to create a basic package by using the SSIS Designer in Business Intelligence Development Studio. You’ll learn how to create the project for the package, add a data flow task to the control flow of the package, and add elements to the data flow.
This video is available with closed captions. To view closed captions, click CC on the video control bar.
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. A 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 will change Integration Services Basic Package.
We'll accept the default location of the project and the other default settings and click OK to create our project.
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, load, and modify 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.
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, which is used to convert the data types of selected columns.
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.
In the Connection Manager's area we'll right-click and select New OLE DB Connection.
Then we will select the AdventureWorks database and click OK to save our settings.
We now have a new connection manager in the package listed as LocalHost.AdventureWorks.
LocalHost refers to the local server. AdventureWorks refers to the database we selected.
The next step is to configure the source transformation and destination components.
We'll configure the source component as follows.
We confirm that the connection manager we created is selected here in the connection manager box.
Next, we select the source from which the component extracts data.
In this demonstration we'll select the Production.Product table.
Then, we're going to select the columns to include from the source.
In this demonstration we'll include the Name, ListPrice, Size, Weight, and SellStartDate columns by clearing all the columns here in the Available External Columns table and then selecting the columns we want to include from the source.
Our selected columns appear here below in the External Column and Output Column table.
Click OK to save your settings.
Next, we'll configure the data conversion transformation as follows.
We 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're going to convert the data type of the SellStartDate column.
We're going to convert the current data type of the column, which is DT_DBTIMESTAMP to DT_DBDATE.
DT_DBTIMESTAMP stores date and time data and the DT_DBDATE data type stores only date data.
Click OK to save your settings.
Now we need to configure the destination as follows.
We confirm that the connection manager we created is selected here in the Connection Manager box.
Then we need to select the source into which the component loads the modified data.
In this demonstration we're going to create a new table by clicking the New button.
When you click the New button, 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 OLEDBDestination_BasicPackageTutorial.
We're also going to remove the SellStartDate column that comes from the OLE DB source.
We're going to keep the SellStartDate column that was modified by the data conversion transformation.
Click OK to save our settings.
Now we'll click Mappings to confirm that the input columns are mapped correctly to the destination columns and then click OK.
Finally, we'll add a data viewer in order to view the modified data that just loaded into the destination.
Now we're ready to run the package.
As you can see, a SellStartDate column that only contains date data, because we converted the data type, is loaded into the destination component.
The package ran successfully.
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 website to view other Microsoft SQL Server videos.