In this SSIS tutorial i will be showing you how to work with Data flow Tasks. We will be loading a text file into the database. We will be working with Flat file and Ole DB connection managers both at the package level and the project level. We will also be looking at Data conversion in order to get our data into the database with the correct datatypes.
- read Lesson 1 if you don’t already know how to create an integration services project.
- SSIS is only available in the “Standard” and “Enterprise” editions.
- If you don’t have your own dummy database with some data in it. You can download and Install Adventure works Database. I will be using Adventure works in all of my examples. Once you have downloaded the Database. Open SQL Server Management Studio -> right click Databases -> Attach -> Select the file that you downloaded.
Put the following into a text file. This is what we will be loading.
BusinessEntityID,AccountNumber,Name,CreditRating,PreferredVendorStatus,ActiveFlag,PurchasingWebServiceURL,ModifiedDate ,NewVender0001,NewVender,1,True,True,,2013-11-13 00:00:00
SSIS isn’t the most stable of tools. I would advise that you save and save often. I have to many times had the program crash on me for unknown reasons and lost work. This probably wont happen on a simple tutorial like this but its a good thing to remember for the future.
We are going to load a CSV file from our pc into the database. The file contains a new vender which we will be loading into a vender staging table.
You will be learning to use the following.
- Flat File Source
- Flat file Connection Manager (Package level)
- Ole DB Source
- OLE DB Connection manager (Project level)
- Data Conversion Transformator
Step 1 Create a project
Create a new SSIS project. If you don’t know how to do that you can read my first SSIS lesson here.
Step 2 Create a Data flow Task
On your control flow add a data flow task. Then double click on it. This will take you over to the data flow tab.
step 3 Adding Source
The Source is where your data is coming from, in our case its the CSV file. Drag a “Flat File Source” on to your canvas. Notice that it has the red X on it again. This is because we need to set it up. Lets do that now.
Double click on the “Flat File Source”. A new window will pop up.
All of the source editors will require a connection manager. A connection manager does exactly what it says it manages a connection to some place. What we need to do here is create a Flat file Connection manger. So click “New”
Now we have to tell the connection manager what file we would like to read. Click the Browse button and a normal browse exporer window will open for you. On the right hand side by the file name change the drop down from .txt to CSV files (*.CSV). Go to the directory you have saved newVender.csv. Select the file and click Open.
There are a lot of settings in this window so you can customize it to the type of file you are loading. Ours is standard so we don’t need to change anything. Before you can click on the OK button you need to click on Columns in the left. Its required so that the system knows which output columns are in the file.
Note: The CSV file has the column names in the first row that’s how it knows the names of our columns. If it didn’t then the column names would just be Column0, Column1 … which makes it very hard to match things up. If you want to test this you can go back to the Genral (on the left) uncheck Column Names in first data row. Then go back to Columns (on the left) see how now your columns aren’t named.
Now click OK and you are back at the Flat File Source Editor.
Now that you are back on the Flat File Source Editor click on columns (on the left).
Here is where you can map the columns. External Columns is the column name coming from our flat file. Output columns is the name given to the column when its sent down the Data flow pipeline.
The Data Flow pipeline is a term used explain when data is sent from one item in the data flow to another. When our next item recieves information the column names will be those in the output column section of this form.
Look at the bottom of this canvas for the Connection managers tab. This is a list of all of the connection managers for this package, only this package will have access to it. If you had created it under the solution explorer it would have been available to all of your packages.
Your “Flat File Source” should now no longer have a Red X on the right. If you can also run the package now if you want. In your “Solution Explorer” Right click “Package.dtsx” select “Execute Package”
The green check means that the item ran successfully. It didn’t really do anything at this point except read the file. Right now your in run mode so you need to stop it by Clicking the blue box in your tool bar or (Shift + F5).
Step 4 Adding Destination
From your tool bar drag an “OLE DB Destination” onto the canvas. Make sure you take a Destination and not a “OLE DB Source”. Notice again that it has a Red X on it.
Click on your “Flat File Source” and select the blue arrow and drag it over to your “OLE DB Destination”. Now they should be linked. What just happened was the Source Adapter told the Destination adapter what columns it was sending.
Double click the “OLE DB Destination”. New window again.
Notice how much it looks a lot like the Source adapter. This time it wants an OLE DB Connection manager. But the form is pretty much the same. With the exception of some 3rd party Tools that you download source adapters and destination adapters look and function pretty much the same. It just depends on the type of connection manager they will be using.
This time we are going to do something a little different when we create the connection manager. If we create it hear it will only be available for this package. I hope to be doing more tutorials we will be making new packages fore each of the tutorials and several of them will probably have to access the database. So instead of having to create a new connection manager each time. Lets create a connection manager that will work for the hole project.
Close the OLE DB Destination Editor “Cancel”
Go to your solution explorer on the right look for the “Connection Managers” folder. Right click on it and select “add New Connection Manager”. Now you have a huge list of all the diffrent types of connection managers you can create. Scroll down until you come to OLEDB. Select that and click add.
Now you have the Configure OLE DB connection Manager window. On the left side you will find a list of all the data connections you have previously created. Click New lets make a new one.
You will need to add a bit of information here. The server Name you want to connect to your login information. Lastly make sure you select which database you will be writing to. Click “Test Connection” to make sure everything is OK. Then Click “OK”
Now you are back on the Configure OLE DB Connection Manager window. You now have a new Data Connection. Select it if it isn’t already and click OK. Notice how under your Connection managers directory you have a new entry for your connection manager. In my case the name is huge. You can rename it which is normally a good idea so you can remember it later I like something like <servername>.<databasename> personally but its up to you.
Back to our “OLE DB Destination” its still got that annoying red X. Double click on it lets go set it up. Notice this time when you opened it it automatically selected your “OLE DB Connection manager” if it didn’t you can always click on the down arrow and select it yourself, or if you have more then one you can change it to the correct one.
Now it knows what database to wright to but it doesn’t know which table. There are two ways of going about this. If you want to be safe you load the data into a staging table and do some checks on it to be sure its right before loading it to the final production table. The alternative is a bit more risky and entails loading it directly into the production table.
If you click the New button next to name of the table or view you can create a new table for your data. If you know what table you want to wright to you can click on the table name drop-down and you will see a list of all the tables in the database. It might take a minute to get the list.
Here is an example of a staging table. We will be inserting into the staging table
CREATE TABLE tSTageVendor([BusinessEntityID] [int] NOT NULL, [AccountNumber] [dbo].[AccountNumber] NOT NULL, [Name] [dbo].[Name] NOT NULL, [CreditRating] [tinyint] NOT NULL, [PreferredVendorStatus] [dbo].[Flag] NOT NULL, [ActiveFlag] [dbo].[Flag] NOT NULL, [PurchasingWebServiceURL] [nvarchar](1024) NULL, [ModifiedDate] [datetime] NOT NULL)
Now go to mappings on the left. As you can see it should have figured out which columns to match where. If the columns in the database aren’t the same as the input columns you will have to fix that yourself. If you don’t it will just ignore that column and you wont see any data. Hint: If you ever wonder why your data isn’t being exported always check your mappings you may have forgotten something. Click OK and you are done setting up the Destination Adaptor
Step 5 Data Conversion
It’s still got a red X, here is where you are thinking OMG she has no idea what she’s doing right? Wrong, this is a problem you are going to run into often
Column “X” cannot convert between Unicode and non-Unicode string datatypes.
When you are reading from a file its non-Unicode a lot of the destination adapters require Unicode. We need is to use a Data Conversion transformer to fix it for us.
Select the blue line linking your source and destination. In your tool box find a “Data Conversion” transformer and drag it onto the canvas. Using your arrows link this together again with the “Data Conversion” in the middle. Yours should look something like the picture below. This is called the workflow in SSIS is basically a set of instructions on what order the program should execute each task.
OK double click the “Data Conversion” transformer we need to set it up. What you have now is the editor. We know from our error message that it was having a problem with the AccountNumber column in the Available input columns list at the top check “AccountNumber”. If you look in the Data Type column it says that its String [DT_STR] we need to fix that. Click on it and change it to Unicode string [DT_WSTR]. In the column “Output Alias” you should see something like “Copy of AccountNumber” change that to say “AccountNumber Unicode” Click OK. For accountNumber set the Length to 15 or you will get a truncation warning.
The red X is still there isn’t it. Open up the Destination adapter again go to columns. Under input columns find AccountNumber and click on it. There is a drop down with another option that says AccountNumber UniCode select that. Say ok.
The red X is still there…. Mouse over it Now its the Name column. We need to do exactly what we did for AccountNumber to Name, PurchasingWebServiceURL
Oh look the Red X is gone now. Right click on the package and select Execute package.
The Red X means that it failed to run the OLE DB Destination. On the top of the canvas find the Progress tab and select it. The progress tab tells you all about what happened when the package executed any errors you will find here.
[OLE DB Destination ] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[BusinessEntityID] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: “The value could not be converted because of a potential loss of data.”.
Got to love Microsoft and cryptic error messages. What its telling you is that it was unable to convert BusinessEntityID. Why would it want to convert BusinessEntityID because its a string and we are trying to insert it into an Int field in the database. This is easly fixed. Open the Flat File Connection Manager at the bottom. Select Advanced on the left. Highlight BusinessEntityID change the datatype to Four-byte signed integer [DT_I4] Click OK. Open your Source adapter on the canvas then go to columns to make sure its picked things up correctly.
Execute your package again. This time everything should have run correctly. We are done check your Database the new vender should have been loaded into your staging table. In another Lesson we will load data from the Staging table into the production table.
Notes from the author
I realize that I showed you a lot of errors while going though this tutorial but if i had just told you what to do you wouldn’t recognize the errors in the future and know how to fix them. I hope you enjoyed this tutorial and are starting to see the potential uses of SSIS in your daily life. I hope to see you in the next tutorial.