SSIS: Exporting Query Results To A Flat File

This is a fairly quick blog post to just go through the process of taking some SQL, running it against a database, and exporting the result into a Flat File. We recently followed pretty much this process to allow us to schedule a non-interactive job to export our entire Financial General Ledger into a flat file to give to our auditors.

The first step is to start BIDS, go to the File menu and select "New Project";

BIDS: New Project Dialog

Select "Integration Services Project", give it a name and select a location to save it to then click "OK". you will then be presented with the new project;

BIDS: Blank SSIS Project

First things first. In the "Solution Explorer" (right-hand side) you'll notice that there is a file called "Package.dtsx". If you deploy this to a server then this is exactly what it will be called - rename it to something else. I usually use the name of the project (without spaces). A dialog will appear;

SSIS: Package Renaming Dialog

Click "Yes", then you'll see something like this;

BIDS: Solution Explorer

Now let's start building the process. Drag a "Data Flow Task" from the Toolbox (on the left) into the designer;

BIDS: Adding a Data Flow Task

Once you've added the new "Data Flow Task" double-click the icon to open it;

Now assuming you're going to be dragging the data down from an OLE DB Source (like me) you need to drag one of those into the designer;

BIDS: OLE DB Source

The little red "x" at the right-side of the new icon means you haven't configured it yet, double click the icon to open the configuration screen;

BIDS: OLD DB Source Editor

The first thing you need to do is connect to your database. This will involve clicking "New" to invoke the "Configure OLE DB Connection Manager" dialog;

BIDS: Configure OLE DB Connection Manager Dialog

In here you can setup connections for SSIS. Once you've created a connection it will be available here for you to use in other SSIS projects.

When you're happy with your connection (i.e. it works!) make sure it's selected and click "OK".

You are now returned to the previous screen with your selected connection highlighted;

BIDS: OLE DB Source Editor with Selected Connection

You have two choices you can either directly pick a table or enter some SQL. I'm going to enter some SQL so you change the drop down from "Table or view" to SQL Command;

The SQL I'm using is;

SELECT *
FROM OPENQUERY ( XXX, 'select * from lot_genealogy_whereused')

This retrieves data (via SQL Server) from our Oracle e-Business Suite. Paste the SQL into the "SQL Command Text" entry box;

BIDS: OLE DB Source Editor with SQL Command

Click on "Parse Query" (to make sure everything is working OK) and then click "OK" at the bottom.

This closes the dialog and returns you to the main window;

BIDS: OLD DB Source (No Errors)

You'll notice that the little red "x" has gone. Now drag a "Flat File Destination" (NOTE it's DESTINATION, not Source) into the designer and connect them up (drag the green arrow from the OLE DB Source to the new object);

BIDS: OLE DB Source with Flat File Destination

You'll notice that the red "x" is back on the Flat File Destination object. Double-click it to configure it;

BIDS: Flat File Destination Editor

To create a new "Flat File Connection Manager" click on "New";

BIDS: Flat File Format

I'm going to use "Delimited" (basically CSV). Click "OK";

BIDS: Flat File Connection Manager Editor

Click on "Browse" to select the destination where you want to place the file. It's important that the destination you select will be visible to the server you're going to deploy it to (using UNC paths if it's on a different server);

BIDS: Flat File Connection Manager Editor

The other thing I change is to add in a double-quote as a Text Qualifier. the main reason for that is if I have data (like mobile numbers) in the source then I don't want Excel treating it as a number when I open it - including a text delimiter solves this problem.

Remember to check the "Column names in the first data row" checkbox if you want them!

Click "OK" when you're done.

BIDS: Flat File Destination Editor

Click on "Mappings" (on the left) if you fancy a look at how the columns in your source are being mapped to the destination;

BIDS: Flat File Destination Editor (Showing Mappings)

Click "OK" to return to BIDS;

BIDS: Designer Window

The designer now shows you that the little red "x" has gone and you're ready to test your package.

Click "Run"/"Start Debugging" and after a few seconds the task will run and you'll start seeing records being written to your file. When the task is complete everything will go green;

SSIS: Task Successful Completion

I'll create a separate blog post for how to deploy the SSIS package to a server so it can be run as a background task (200k rows took around 10 seconds, but if you had 20m you wouldn't want to sit there watching it).