SSIS: Lesson 1 – Export to CSV

SSIS Tutorial Lesson 1

Exporting Data to a CSV file

One of the main purposes of SSIS is moving data from one location to another with ease. In this lesson we are going to export data from our SQL Server database to a CSV file.

If you want to follow along, you can use the following code to create the data table I will be working with.  I loaded this into a database I created for practice called Sandbox.

CREATE TABLE employee_id (        
             emp_nm nvarchar(30) not null, 
            emp_id nvarchar(8),
             b_emp_id nvarchar(8)  
     PRIMARY KEY(emp_nm) );
INSERT INTO employee_id       
       (emp_nm, emp_id)
VALUES       
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568')
INSERT INTO employee_id       
        (emp_nm, b_emp_id)
VALUES       
        ('Priyanka', 'B1234567');

We will start by opening the Training_Project we created in the intro lesson (SSIS Tutorial: Introduction )and creating a new package.

2018-03-08_10-00-21

I renamed my new package Lesson_1

2018-03-08_10-01-27

Now we need to make a  new connection so that SSIS knows what our data source will be communicating with. To do so, go to Solution Explorer, right click on Connection Manager and select New Connection Manager

2018-03-08_10-06-41

Since we will be connecting to a SQL Server, select OLE DB from the list below

2018-03-08_10-07-17

Since there are no existing connections to pick from, choose New

2018-03-08_10-07-35

Okay, starting from the top select Native OLE DB\SQL Server Native Client 11.0  (note you might have 10.0 – that will work as well)

Since my SQL Server is locally installed on my machine, I am using Localhost as Server Name, otherwise provide the server name here.

Select your database from the drop down, again my database is Sandbox

Finally, hit Test Connection, you should get a connection successful message box.

Click Okay

2018-03-08_10-10-11.png

You’ll see your new connection in the box, now click Okay

2018-03-08_10-10-55.png

Now at the bottom of your Design Window, you’ll see your new connection in the box labeled Connection Managers

2018-03-08_10-11-15

So next, we need to go to the SSIS Toolbox and drag a Data Flow Task over to the designer

2018-03-08_10-14-02

Once in the designer, click on the Data Flow Task box, this will bring you to the Data Flow window

2018-03-08_10-14-21

Data Flow is used whenever you need to move data between disparate systems. Since we will be moving data from SQL Server to a CSV file, we need to use a Data Flow Task

You should note that the SSIS Toolbox has changed, offering up new Data Flow related tools.

Scroll down to Other Sources and drag OLE DB Source to the design box

2018-03-08_10-14-51

Double click on the new object

2018-03-08_10-15-15

Make sure your Connection manager you just created in the top drop down. Leave Data access mode at Table or view and select dbo.employee_id as your table

2018-03-08_10-15-52

If you click Preview in the bottom left, you will get a pop up of the data in the table

2018-03-08_10-16-08

If you click Columns in the upper left, you will see the columns that will be exported from the table. You can change the Output Column names if you want to use a different name in your output file.

We will skip over Error Output for now, as that is easily an entire lesson all its own.

2018-03-08_10-16-28

Now go back to the SSIS toolbox and under Other Destinations, click on Flat File Destination and drag it over to the design window.

2018-03-08_10-16-54.png

Drag the blue arrow from the OLE DB source box to the Flat File Destination Box

2018-03-08_10-17-18

It should look like this when done

2018-03-08_10-17-39

Now click on Flat File Destination

Since we don’t have a current Flat File Connection, we will need to click on New to create one.

2018-03-08_10-18-04

Select Delimited and click OK

2018-03-08_10-18-42.png

Find a folder you want the file to end up in. Select CSV files from the bottom right drop down, and name your file.  Click OK  (Note, use a name of a file that does not currently exist. This will create the file)

2018-03-08_10-22-56

Check the box: Column names in first data row

2018-03-08_10-23-41.png

If you click on Columns in the upper left, you will see the names of your header columns.

Click Okay to go back to the Flat File Destination Window

2018-03-08_10-24-14.png

If you click on Mappings, you will see you have 3 columns from your source going to three columns in what will be the new CSV file.  In future lessons I will show how you can use this to match up different named columns.

Click Okay to return to the design window

2018-03-08_10-24-35

Go to Solution Explorer, right click on the package and click Execute Package

2018-03-08_10-25-11.png

You should get green check marks to indicate success. This is a very small package, so they probably turned green instantly. In larger jobs, the green check will first be a yellow circle to indicate progress. It turns green when that step is complete.

Note on the connecting line between Source and Destination that you get a read out of how many rows were processed.

2018-03-08_10-25-34

Go to the folder you chose as your destination, you will see your new CSV file there

2018-03-08_10-26-36.png

Open the file, you will see your data has been exported into your CSV file

2018-03-08_10-27-35.png

Advertisements

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s