In this tutorial we will create a simple SSIS package. In this package we will load data from one SQL Server table into another SQL Server table. We will use a Data Flow Task with a OLE DB Source and OLE DB Destination. We will connect to our database with a OLE DB Connection.
Before getting started I created this database table in the AdventureWorks datbase, to use to load into in this package.
Creating your first project:
To being, launch SQL Server Business Intelligence Development Studio (BIDS) from the Microsoft SQL Server menu in the start menu. Once BIDS starts up find the "Recent Projects" section and then find and click on "Create: Project...".
After clicking on the the Create Project link, you will be provided with the "New Project" window. Select "Integration Services Project". There are textboxes at the bottom for which you can provide a name for your project, location for your project and the solution name (a solution is used to group multiple SSIS packages together). For the purposes of this tutorial I will choose the default settings.
After clicking "OK" on the "New Project" window you will be provided with your new Integration Services project opened up. You will most likely see the "Solution Explorer" on the right which will contain all of the SSIS packages in your solution. Since you just created a new Project you will only have one package in the solution, but you can add more later.
We will begin development, by creating the connection to our database. We will right click in the box labeled "Connection Managers". This will display a context menu that will allow us to select the type of new connection that we want to create. Let's select "New OLE DB Connection".
A window will be provided to configure the new OLE DB Connection Manager. Click on the "New.." button to create a new Data Connection.
The Connection Manager Window will appear and you can select the "Provider" that you would like to use. I will use "Native OLE DB\SQL Native Client" because I am using SQL Server as a source. Other providers are available to connect to other data sources. Once you have selected the Provider, enter the Server Name of the database that you want to connect to. Provide the login details of the server. and then select the Database that you want to connect to on the server that you provided at the top. You can hit the "Test Connection" button if you want to ensure that you can properly connect. When you are done setting up the connection manager, click on the "OK" button at the bottom.
You will see that the new Data Connection has been created in the "Configure OLE DB Connection Manager" window. Click "OK" to finish creating the Data Connection.
Now that we have the Connection Manager created, we can begin to create our data flow. The "Toolbox" window may already be visible, or you may have to expand the tab, labeled "Toolbox". If the tab was collaped, you can "pin" (circled in blue) the Toolbox, to the screen so that it doesn't collapse back again. The "Toolbox" contains all of the available tasks that you can use in SSIS.
The first Task that we will use is the Data Flow Task. The Data Flow Task is used to take data from a Source or Sources and send data to a Destination or Destinations. Transformations of the data can take place after loading data from a Source and before loading it to the Destination.
Select the Data Flow Task from the Toolbox and drag it on the "Control Flow" area of the screen. Once you have dragged it on to the Control Flow area, let go of it and you will see it appear on the screen.
Now double click on your new Data Flow Task. You will see that the package will change from the "Control Flow" Tab to the "Data Flow" Tab. You are now inside your Data Flow Task. Also look at your Toolbox. You will see new items available. Let's now select an "OLE DB Source" and drag it on the "Data Flow" area and let go. You will see the new OLE DB Source component is created. This will be where we obtain our Source data.
Double click on the new OLE DB Source component and the OLE DB Source Editor will appear. Select the OLE DB Connection Manager that we created at the beginning of this tutorial. The Data Access Mode will allow you to create a query or load directly from a table. I'm going to select "SQL Command", because I know the query that I want to use to select my data from the Source Table. You can hit the "Preview" button at the bottom to preview rows from your query or table. Click the "OK" button at the bottom of the screen when you are done setting up the OLE DB Source Editor.
Now select the OLE DB Destination component and drag it onto the "Data Flow" area, below the OLE DB Source. You will see the OLE DB Destination be created on the screen.
We want to be able to use the data from the OLE DB Source and send it to the OLE DB Destination. So click on the OLE DB Source, and you will see a green arrow appear below the OLE DB Source.
Select this arrow and drag it until it is over the OLE DB Destination and then let go. If you properly dragged the arrow, you will see the OLE DB Source and OLE DB Destination connected by the green arrow.
Once the OLE DB Source and Destination have been connected, double click on the OLE DB Destination. The OLE DB Destination Editor will appear. Select the OLE DB Connection Manager that is used as the destination database. In my case, I am loading data to the same connection as my source, so I will use the same Connection Manager. I'll select Data Access Mode: "Table or View", and I will select the table that I created in my pre-work.
Now, with the OLE DB Destination Editor still open, we will select the "Mappings" option on the left hand side of the window. This will allow you to map the Columns from the Input of the OLE DB Destination to the Destination. If the column names and data types match, SSIS will automatically map the columns to each other. If your column names are slightly different you will need to map them manually. If the datatypes of the input column and the Destination column are not compatible, you will have to convert the input column accordingly. Data conversion is beyond the scope of this tutorial, but will be covered in future tutorials. When you have successfully mapped your columns to the Destination columns hit the "OK" button.
Now that we have set up our Source and Destination, we can test out our new package. Hit the "Start Debugging" button on the top of BIDS. It looks like a play button. Or if you prefer, you can hit the F5 button, to start debugging.
Once a component or task starts working in SSIS you will see it turn yellow. Once it has completed successfully it will turn green. If you incounter and error in SSIS the task or component will turn red. As you can see, my package ran successfully and loaded 100 Rows from my OLE DB Source into my OLE DB Destination. You can click the stop button when you have finished debugging. Hopefully you were also able to successfully create your first package!