- Visual Studio 2010
- Apache License, Version 2.0
- SSIS
- SQL Server Integration Services
- SSIS 2012
- SQL Server Integration Services 2012
- Getting Started
- 07/01/2012
- Copy a data file to c:\SSIS . The file is named Product data.csv, and is contained within the sample archive.
- Create a table in a SQL Server database into which you have permission to insert data.
CREATE TABLE [dbo].[Products]( [ProductCode] [int] NOT NULL, [ShippingWeight] [float] NOT NULL, [ShippingLength] [float] NOT NULL, [ShippingWidth] [float] NOT NULL, [ShippingHeight] [float] NOT NULL, [UnitCost] [float] NOT NULL, [PerOrder] [tinyint] NOT NULL ) ON [PRIMARY] GO
If you'd prefer to read along rather than listen to the video, a rough transcipt is below:
I’ve just created a new Integration Services project in Sql Server Development Tools.
The center pane is the design canvas. This is where I’ll build the logic that performs my data integration.
The left pane contains the SSIS Toolbox. The toolbox contains the things I use to build the data integration logic.
In SSIS all the data integration logic one constructs is contained within “Packages”.
Since I’ve just created this project, it has a default package named “Package.dtsx”, which is what I’m looking at, now.
Each package contains a single “Control Flow” design surface.
You can tell that’s what I’m looking at because this tab is selected.
Now I’ll add something to the design surface: A Data Flow Task.
I added the Data Flow task to the surface by double-clicking it in the toolbox, but I could have dragged and dropped it from the toolbox to the canvas if I wanted to place it somewhere specific.
This Data Flow task doesn’t do anything, yet, because I haven’t configured it.
I now double-click the Data Flow task to configure it.
Notice two things that just happened:
First, the Data Flow tab is now active.
Second, the toolbox contents have changed.
These are the Data Flow Components from which a Data Flow is constructed.
I’ll now construct data flow logic that loads data from a CSV file into a table in SQL Server.
I start by adding the Source Adapter. I’ll use the Source Assistant for this. I activate the Source Assistant by double-clicking it in the Toolbox.
I follow the instructions in the Source Assistant.
I pick the file that I want to use.
The rest of the information on this page looks correct. But I see this instruction to specify the Column information for my file, so I switch to the Columns page to review the columns SSIS has detected.
This information looks correct, as well, so I could click OK, now.
However, the Flat File Source adapter reads in the contents of files as strings, by default.
Since I know that I’m going to be loading this data into a table in SQL Server that has typed columns, I want the source adapter to convert the strings it reads from the file into numbers.
So I switch to the Advanced page and tell SSIS to suggest data types by scanning the file.
I can review the suggestions here.
These look good, so I click OK.
Now a Flat File Source adapter has been created for me and placed on the canvas.
This represents the entry point of data into this data flow.
Next I’ll add a destination adapter which will define where data leaves the data flow. I’ll use the Destination Assistant, again by double-clicking it in the toolbox.
I follow the instructions in the Assistant.
I’ll be using my local server.
I specify the database.
Now a destination adapter has been added to the canvas.
I’ll reposition it by dragging it.
At this point there’s an error, which I can read by hovering my mouse over the error icon: The message indicates that a destination table has not been specified.
Before I edit the component to address this error I’m going to connect it to the source of the data. Let me explain why:
Right now the destination adapter isn’t associated with any source of data. These two components on the canvas don’t know about each other. By connecting the source to the destination adapter, I enable the destination adapter to figure things out about how to configure itself. For this reason it’s a good idea in general to connect components to the upstream sources of data before configuring them.
To connect the destination adapter to the source for its data, I first select the source. Notice the two arrows that appeared. The blue arrow represents the normal data output and the red arrow represents the error output. Ignore the error output, for now, since it’s the normal data output I’ll be using.
I click the output, move over onto the destination adapter, and click again to connect the two.
Now I configure the destination adapter.
I double-click the destination adapter to open its editor.
The instructional message at the bottom of this window tells me I need to specify the name of the table.
I pick the table.
Now the instructional message tells me that I need to provide a column mapping, so I switch to the mapping page.
A destination adapter typically knows about two sets of columns: The columns that enter it from upstream, and the columns that exist in the external data store that it will load data into, in this case the SQL Server table.
The mapping being defined here is between these two sets of columns.
You might recall that these columns names on the left side are the column names that I saw earlier in the Flat File Source adapter editor.
The column names on the right came from the table I selected on the previous page of the destination adapter editor. To establish the mapping between the columns I can drag and drop the columns, or I can specify the mappings in the grid below.
My data flow is now defined, and ready to transfer data.
To run the package inside the debugger, I click this “Play”
button.
These green check marks indicate that each component has completed successfully. This count represents the number of rows that transferred along this path.
To leave debugging mode and return to package editing mode I click this “Stop” button.
REVIEW
Data Integration logic is defined on canvases using items from toolboxes.
Control Flow and Data Flow are defined separately, and Data Flow is defined within Data Flow tasks.
Within a Data Flow there are source adapters that bring data into the SSIS data flow as well as Destination adapters that export data out of the Data Flow to external data repositories.
The Data Integration logic can be run inside the development environment.