Project Developing Steps in SSIS
This page covers more advanced project developing process in SSIS that is continued from here.
Creating a Flat File Source Task and OLE DB destination
In the Data Flow Task, the SSIS toolbox changes when entering it. Reviewing the tool boxes for the available tasks could be helpful in applying the developing steps to achieve the desired results.
Control Flow
Data Flow
Collapsing all of the menus in the Data Flow toolbox and ignoring Favorites and Common, the remaining three are called Sources, Transforms and Destinations.
Setting Up the Data Flow Task
Setting the Flow Task is done by going to Other Sources for setting and selecting the Flat File Source and dragging it into the working area. This will point to the file preferably a CSV file with the data.
The next step is to go to Other Destinations > OLE DB Destination and drag it into the workspace.
Go to Other Sources for setting
Select the Flat File Source
Drag it into the working area.
This will point to the CSV file with the data.
Go to Other Destinations > OLE DB Destination and drag it into the workspace.
This will point to a table in the database where the desired location is for loading the data into.
The source and destination could be renamed if desired, but not necessary.
The last step is to connect the two. There are two arrows coming out of the Flat File Source.
The blue arrow is where the data goes and the red arrow is where any error messages will go.
Blue arrow is the one needed for connecting to the Destination.
The Data Flow is prepared.
Setting up the Flat File Source connection
For setting up the flat file source connection, the first step in the process is to take the file that is needed to be uploaded and place it into the uploads folder that is part of the project folder.
The file is prepared previously and is located in the Prepared Data folder.
Creating a subfolder with its creation date as its name in the Uploaded Data folder to have an organized project data folder.
In this step copy the folder path and going back to SSIS, double clicking the Fat File Source box. In the Flat File Connection Manager, click on the browse and paste the path. By default the tool is looking for .txt files and changing the file format in the search window to the format of the file, e.g. CSV, is the key to find the desired file.
After the file is found, the Flat File Manager should be set up first by changing the connection manager name to something recognizable, the name of the file would be sufficient and then changing the text qualifier from none to quotation marks.
This ensures that data in the .csv file is recognized correctly. It is also important to make sure that the check box saying column names in the first data row is checked. It means that the first row of the file contains headers and the actual data starts from the second row. No other controls in this window are needed.
In the left part of the window, there is Columns option and Preview. There is a preview of the data. The CSV file is very simple. The row delimiter will always be the carriage return linefeed sequence and the column delimiter will always be a comma,”,”, which means that to not change these two and leave them at default.
For previewing the data, scroll as far to the right as possible and check for two things.
Make sure that the last column is the same one as in the file that was prepared.
a. There are no extra empty columns to the right.
b. If extra columns are found on the right, that shouldn't be there, it means that the CSV file contained empty columns and the previous step preparing a specific file before the load should be revisited. .
c. If those columns are not required, they should be deleted.
Scroll down as far as possible and look at all of the cells in the last column and see if they have the correct type that is expected based on the column's name in this case, a number.
a. This check is to make sure that none of the rows available in the preview got skewed because of a data anomaly.
Finding an error this way will save a lot of time down the track. If all is good, then move on to the advanced step in the menu on the left.
This is a vital part of the upload.
The data types for the columns could be specified in this part as many analysts do.
All the columns will be uploaded as text, no matter if it is a name, date or number. Everything will be uploaded into the database as text and then in the database it will be sorted.
Clicking through the columns will show that all of them have already been recognized by SSIS as text. This is indicated by the data type string as shown in the figure.
It is a good start. But there is one little problem. SSIS wants to save space and therefore has only allocated 50 characters to every single data element which is not much at all. If there is a cell that contains 51 characters, then the upload will produce a truncation error.
The memory allocation should be increased to a thousand characters, that is the safest bet by far. There could be rare occasions when data that has more than a thousand characters comes across. An example of that would be if a column contains free form text such as customer complaints or comments. Select all elements in the column section and output column, and change the value to 1000. Now, all of the data elements will have 1000 characters of storage space.
Setting up the database connection and creating a RAW table
To set up the database connection in SQL, open the SQL Server Management Studio, and connect to the desired server. Continue with locating the database that will be used.
As shown here, the database DSDemo will be used. By opening the Table folder created tables could be seen. At this stage, the server name is needed.
To continue the setup, return to Visual Studio. For setting up the database connection go to the Data Flow window and double click the OLE DB Destination icon.
As it was done previously with the Flat File Source manager, creating a new OLE DB connection manager is needed which then could be used in the OLE DB destination.
The process is to click on new, and connecting to the server and the database. And continue with clicking on new again and typing in the server name, and clicking Refresh. Database could be selected from the dropdown menu and continue by making sure the authentication method is correct.
It is much quicker to set up an OLE DB connection manager than a Flat File Source manager. The best part is that this connection manager could be used in the future OLE DB destinations within this SSIS project because the same database most likely will be used to store the data.
The OLE DB connection manager is alongside the Flat File Source Connection manager. Now the server and the database have been connected, the last step is to create a table where the data will be placed. There are two options here. Either creating a table mainly from within the server management studio or from Visual Studio, getting SSIS to do most of the work. If the first method is used, after creating a table in the SQL Server management studio, it can be selected from the drop down menu.
However, the second approach is recommended where the table is created through Visual Studio. It is more efficient and nicely fits into the ETL blueprint. To create the table click new. SSIS is smart and has already prepared the code. The table that will be created will have the exact columns that are needed.
To create the table click new. SSIS is smart and has already prepared the code. The table that will be created will have the exact columns that are needed.
The SSIS knows what columns are needed and what size they should have because of the blue arrow that connects the Flat File Source and the OLE DB destination.
The blue arrow sends information to SSIS that this table in the database will be used to store the data coming from the Flat File Source. Therefore, SSIS knows how the tables should be structured. This can be very handy and save lots of time when dealing with large tables with hundreds of columns.
Going back to the table creation menu, everything is almost ready. Only a few small changes need to be made. Changing the name of the table as shown, where this naming convention will show the viewer of the database that this table contains raw data for the analysis. In the case of refreshing the data analysis, distinguishing between the old and new data would be easy based on their date.
Another major reason is auditing, where it is always known in which subfolder of the uploaded data folder, the matching CSV file is located. This is important because it allows the tracing of any errors back to the source.
When creating the table, it would be helpful to add add an extra column by entering this text: [RowNumber] int identity (1,1), which is the code for inserting a row number column at the start of the table. It is recommended for every table created in the SSIS.
This helps with indexation, especially with large datasets. Basically what this means is that they become more efficient and they take up less space. As soon as the OK button is clicked, SSIS will create the table.
To check for the added table, right click on the tables folder and hit refresh. The new table is added. Looking around the table e.g. top 1000 rows. In SSIS the OLE DB destination setup is finalized as the newly added table to the dropdown, following it with mapping the columns. Confirming it by clicking OK as OLE DB destination is ready.