Hi there, I'll be showing you the steps on how you can perform an ETL (Extract, transform and load) process on a flat file containing data to SQL Server using SSIS.
To extract data from flat files (such as CSV, txt or other delimited text files) and load it into SQL Server, you can use various methods and tools available. This is how i went about it.
I will be assuming that you have your SSIS (SQL Server integration Services) tools in your system, if not do well to install it to your laptop. Navigate to this link for instructions on how to install SSIS. https://learn.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver16
Once your done with installation phase.
Step1- Navigate to your visual studio, then create a new project, ensure you select the integration service template
Step2- Right click on your packages to create a new package, you can name it what ever you like.
For my case here, i will be right clicking on the Project1-SSIS. From then on we are good to go.
Step3- We will need to create a connection manager for the flat files and also for the SQL server database destination. Right click on the connection manager tab below and you will be given different options. in our case we will choose the flat file connection. once you do that there will be an empty prompt for you to fill out.
You can choose a name of your choice for the connection manager name, then browse out the flat file location. ensure you navigate to the columns for mapping. after that lets create a connection manager for SQL Server. This connection manager is called OLE DB connection.
Before doing this, hope you have created a database in the SQL Server if not, try and do so. Once you have done that, navigate to the OLE DB connection manager and click on new, then the database you created will be visible, select it. At this point you have created two connection managers. One for the flat file and one for the SQL Server database. By the way for starters i will recommend you always try to copy your excel or CSV data file to a notepad, this will help you scale through Microsoft excel connection managers headache.
Step4- At the top left which is the SSIS tool box, drag the dataflow task, then double click.
Still at the SSIS tool box inside the dataflow we will need to look for flat file source and OLE DB destination, we will drag and drop both of them.
Then after that, it is time to make some connections.
Step 5- Lets double click on the flat file source, to initiate a prompt
Automatically it links up with your connection manager, now navigate to the columns to view your columns, you can actually choose how many columns you need or want to use. After this you will click on ‘OK’ button, then create a link to the database connection manager by dragging the blue arrow to connect with it. Then double click the OLE DB destination.
if you notice, it has already identified its connection manager, this will be this way, if you are dealing with a single source or single destination manager. if its multiple source or destination mangers , you will have to select it manually.
Step 6- We will have to create a new table in the database by clicking on new, it will show a create table SQL code, do well to edit it.
Once you click on the ok button a table will be created in the sql server, do well to check your columns for mapping. once you are done, you will notice the error signs are all gone.
Now you can run the package by right clicking on the package and clicking execute task, or you can simply click on the start button. with this your data will imported to your SQL Server.
Since we want to do an ETL process, we will do a bit of a simple transformation. lets select the sort task. Drag the sort task and connect it in between the flat file and OLE DB destination.
Step 7 — We will stop the package from running, and also check your database to confirm that the table you loading has been loaded to the database , you can confirm this by writing a simple count(*) SQL code.
We have dragged and dropped the sort task to our dataflow, now lets remove the error signs.
Click on the sort and choose the column you are sorting by. This is equivalent to the order by statement in SQL.
i am sorting by country. you can click on ok and run your task. do well to confirm your database if data has been sorted.
We have now created a simple ETL package.
Before we wrap up, if you count data in your SQL server you will notice that the numbers are increasing. To solve that situation we will have to navigate back to the control flow, then drag in the SQL task and connect with our dataflow. The SQL statement will have to be Delete from table_name.
I hope this was helpful, do well to follow me, as i will dropping more contents on ETL and data engineering. Thank you….
