Data Warehousing and Business Intelligence

5 June 2011

SSIS: Export a Query Result to a File

Filed under: SSIS — Vincent Rainardi @ 11:01 pm
Tags:

This seems to be a simple task but someone who just begins using SSIS will surely come across this task, and at that point he will need an example to copy from. Importing a file into a table is a more common task in Data Warehousing than exporting to a file, so it is possible that an SSIS developer can do:

  • Import a file into table
  • Run SQL to copy table1 to table 2
  • Export a table into a file

but never to this day he exported the result of a query to a file.

There are 2 ways to do it:

  1. Using Data Flow task
  2. Using Export Import Wizard

I recommend no 1 over 2 as we have more control but I’ll go through both. No we can’t use Execute SQL Task for this. Nor OLE DB Command Transformation.

Using Data Flow Task

Pull the Data Flow task into the Control Flow tab and double click it to edit:

On the Data Flow tab, pull the OLE DB Source and a Flat File Destination then connect them:

Double click the OLE DB Source to edit it:

Set the OLE DB connection manager to an existing one or click New to create a new one.

This is the most important one: Set the Data access mode to SQL Command. Type the SQL query on SSMS, test it then copy and paste it here. You can also use “Build Query”:

Click Preview to see the query result. Click Columns on the left hand side to check the output columns (this is optional) and then click OK.

Double click the Flat File Destination to edit it.

When asked, select the file format as Delimited. It is the most common. You can experiment with the other types later.

Click on New to define a new Connection Manager and set the file name by clicking Browse. Select “Column names in the first data row” as needed and change the connection manager name to one of your choice.

On the left hand side click on the Columns and set the Column delimiter to Vertical Bar {|}, see below. Again this is the most common one; you can experiment with the other delimiter later on. Then click OK.

Run it (Start Debugging) and check the file output. I would suggest that you only put 3 rows of data on your table so you can check if you have 3 rows on the output file. If you have 10,000 how do you check it? 🙂

Using Export Import Wizard

On the Solution Explorer, right click on the SSIS Packages and select SSIS Import and Export Wizard. Navigate through the pages.

1. Source: for SQL Server 2008 choose SQL Server Native Client 10.0, and select the DB as below; otherwise choose as appropriate.

2. Destination: choose Flat File and specify the File name. Choose “Column names in the first data row” if needed, as below:

3. Specify Table Copy or Query: choose Write a query to specify the data to transfer, as below:

4. Provide Source Query: type the query in SSMS and copy paste the query here, as below:

5. Set the column delimiter to Vertical Bar. This is the most common one. You can experiment with other delimiter later on.

6. Click Next, Finish, Close.

On the Solution Explorer you should have a new package, e.g. Package1.dtsx. It contains 1 Data Flow. The Data Flow contains an OLE DB Source and a Flat File Destination. It is just like the package we created a little earlier.

Hope this helps.

Picture for “skipping to the next task”: (see dsentelle’s comment below):
Skip to next task

 

11 Comments »

  1. […] exporting a query result into a file Link […]

    Pingback by Coming SSIS Articles « Data Warehousing and Business Intelligence — 5 June 2011 @ 11:06 pm | Reply

  2. This does feel like an over complicated solution to a problem. I’m aware that this does provide exceptional scope for mixing results into output files, power for integrating other logic and SSIS goodies into the creation of an output file.

    But the functionality that I find is lacking is a more simple solution to taking the output of a one off query and sending it to a csv file (without a third party tool). Or have I missed something?

    Comment by David — 6 June 2011 @ 10:02 am | Reply

    • Hi David, you are right, from SSMS (Mgt Studio) you can export the query output to a file. What I meant in the article was building an ETL package for a DI (Data Integration)/DW project, which will be executed every day to export the data.

      Comment by Vincent Rainardi — 6 June 2011 @ 1:48 pm | Reply

  3. This is exactly what i was looking for , We have one of our source database as SQL Server and our DataWarehouse is Oracle RAC and there is some internally built API , with the help of which i can upload only tab delimited files to the DW

    My requirement is , every day at the end of day , i have to run some sql queries on SQLServer and output the data in tab delimited format and then upload these files using API , the 2nd part i can automate it , can you please let me know , how can we automate the first part i.e scheduling this task daily so that automatically files will be available everyday at ‘X’ time

    Comment by Rajiv — 16 February 2013 @ 2:35 am | Reply

    • Thank you for your comment Rajiv. To automate the export of data from SQL Server into a tab delimited file you can schedule the SSIS package to run automatically at X time using a SQL Server Agent Job.

      Comment by Vincent Rainardi — 16 February 2013 @ 9:06 am | Reply

    • I’ve just replied on the other thread (you can use SQL Server Agent), or you can use Autosys if your company has it.

      Comment by Vincent Rainardi — 16 February 2013 @ 9:08 am | Reply

      • Thank You very much Vincent for the quick reply , i will look into that solution proposed

        Comment by Rajiv — 17 February 2013 @ 6:00 am

  4. sorry for the spam vincent , i was unable to find the article that you were talking about sql server agent job , can you please share the link please

    Comment by Rajiv — 17 February 2013 @ 6:04 am | Reply

  5. Great, simple article, told me exactly what I needed… and led right into my first question. Let’s say your query result is empty. I’ve found SSIS dislikes this very much. How do you handle that error? I can’t figure out how to set it up so that if the result set is empty, it just goes onto the next task.

    Comment by SSIStarter — 8 July 2016 @ 8:16 pm | Reply

    • Hi Sentelle, you can do this using Precedence Constraint. Create 2 tasks. Task A is an Execute SQL Task that reads the rows from the table. Task B is a Script Task that write the rows into a file. Double click the arrow between the two tasks. On the Precedence Constraint Editor, set the Evaluation Operation to Expression and set the Expression to @NumberOfRows >= 0. In the Execute SQL Task (on the Result Set pane), populate this @NumberOfRows variable with the row count from the Result Set. You will now have an fx on the arrow between the two tasks. This way when the number of rows in the result set is 0 then the flow would not go to the next task.

      Now create another task (Task C) that you want to execute after writing to file. Connect Task A to Task C using an arrow with fx like above, but the Expression is “@NumberOfRows == 0”. Connect Task B to Task C as well. Now on Task C’s Precedence Constraint Editor you can see there is an option called Multiple Constraints. Select “Logical OR” instead of “Logical AND”, because Task C can be reached from either Task A or Task B. Setting it to Logical OR makes the line becomes dash rather than solid.

      This way, when the number of rows in the Result Set in Task A > 0 it would go to Task B (Write to file). And if the number of rows in the Result Set in Task A is 0 it would bypass Task B and go straight to Task C.

      I have included an picture at the end of my article above to illustrate this.
      The key thing here is you can’t do Precedence Constraint within a Data Flow Task. You need to do it in the Control Flow. Within a Data Flow Task, the arrow between a source and a destination (and a transform) is a Data Flow Path. It is not a Precedence Constraint where you can set an expression.

      Please also see these 2 articles which I think are relevant:
      http://stackoverflow.com/questions/7281504/how-to-proceed-to-the-next-task-only-if-no-records-exist-for-a-given-query
      http://stackoverflow.com/questions/3819086/how-two-different-task-can-goto-one-task-in-ssis (just the first picture marked as “number 7” is relevant).

      Comment by Vincent Rainardi — 13 July 2016 @ 7:14 am | Reply


RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: