Rajendra Gupta
Understand logic

Using SSIS packages to add row headers and data into flat files

June 4, 2020 by

In this article, we will configure an SSIS package to generate a composite output in the flat-file destination.

Problem overview

Recently, I received a requirement to get an output of a query in a text file. It is simple to get an output in the flat-file using SSMS options.

  • Results to text
  • Result to file

But my requirement was a little different. Let me explain the output format first. In this article, I will use the [AdventureWorksDW2017] sample database. You can download the backup copy from the Microsoft docs and restore it in the SQL instance.

Query to get sample data

In the above query output, the first column shows the [ModelRegion]. Our requirement is to get output in the following format. It should show [ModelRegion] as a header followed by data in the corresponding rows in the next lines.

Final Output

If you have small rows in the SQL table, you can get all rows in a text file and manually format the data. But I do not want to prepare data manually. SSIS package always comes for rescue in these situations. Let’s see how SSIS can satisfy the requirement.

You can go over these articles here, Integration Services (SSIS) to gain basic knowledge of SSIS packages before proceeding with this article.

Create an SSIS package to get output in a flat-file with column header

You should have a visual studio 2019 with SQL Server Data tools and integration service extension. You can refer to this article, Install SSDT with Visual Studio 2019 for more details.

Create a new SSIS project using the Integration Service project option shown below.

Create a new integration service project

Specify the project name and location as per your requirement.

Specify the project name

Define variables

In this project, we define user variables. An SSIS variable is an object that stores the values, and you can reference these variables in the package. SSIS provides a few system variables as well, and you can use these system variables, especially for auditing and debugging purposes.

Let’s create the following user variables for our SSIS package configuration.

  • User::ModelRegion: In this variable, we store the distinct values of [ModelRegion] column from the below query

  • User::QueryDetails: In this variable, we store the records for a corresponding [ModelRegion].

To define a variable in the SSIS package, right-click on the blank area of Control flow and select Variables.

define a variable

In the variables window, click on Add variables( as pointed by the arrow) and add the variables defined above. You can choose appropriate data types for a user variable.

Add variables

Add an execute SQL task

Now, drag an execute SQL task in the control flow and open its properties. In the execute SQL editor, make the following configurations.

  1. Name: Specify a suitable name for the task
  2. Result set: Select the Full Result set from the drop-down. It this case, the result set can contain multiple values
  3. Connect details:
    1. Connection type: OLE DB
    2. Connection: Create a new SQL connection or choose an existing SQL instance connection, authentication method (Windows or SQL), and default database for SQL OLE DB connection
    3. SQL Source type: Direct input. We use the SQL query in the direct input method
    4. SQL Statement: Here, specify the SQL query to get distinct [ModelRegion] values

Add an execute SQL task

Click on the Result set in the left-hand menu and map the variable User::ModelRegion. Here, you always use value 0 for the full result set.

Result set and variable

It completes the configuration for executing the SQL task.

SQL task

Add a Foreach loop container

Now, drag the Foreach loop container and join it with executing the SQL task created earlier. It runs the tasks specified inside the loop for each value received from executing the SQL task.

You can refer to this article, Using SSIS ForEach Loop containers to process files in Date Order to learn about this container.

Add a foreach loop container in the SSIS package

Double click on this Foreach loop container to configure it. On the collection page, make the following changes.

  • Enumerator: Select Foreach ADO Enumerator to loop through the list of distinct values of [ModelRegion]
  • ADO object source variable: Select the variable User::ModelRegion from the drop-down

Foreach loop container

Click on the Variable Mappings and map the variable User::QueryDetails from the drop-down values. It automatically takes value 0 for the index column.

Variable Mappings

Add data flow tasks in the Foreach loop container

Now, drag a data flow task inside the for each loop container. You can rename this data flow task, but let’s go with a default name.

Add data flow tasks

Open the data flow task editor and add the following components.

  • OLE DB Source:

In this source, specify the SQL instance connection and paste the following SQL query and filter records with parameters

OLE DB Source

Click on the Parameters and map the SSIS variable, as shown below.

  • Parameters: Parameter 0
  • Variables: User::QueryDetails
  • Param direction: input

Parameters

Click Ok and add a flat file destination. Here, the red cross shows configuration is not complete for the task.

Add a flat file destination

In the flat file destination editor, specify a text file name and location. We can leave the other configurations such as text qualifier, header row delimiter as default.

flat file destination editor

Click on Mapping and verify Mapping between SQL query output and flat-file column. In this demo, we have input and output columns as [ModelRegion].

verify Mapping

Now, go back to the control flow area and add another data flow task. This data flow task will add the details for a particular [ModelRegion] in the flat file.

data flow task configuration

Open the data flow task editor for it and add the same OLE DB source and flat file destination.

In the OLE DB editor, specify the SQL query that returns values for data rows corresponding to a [ModelRegion].

For this demo, I added the top 2 clauses to show output with the top two rows as per the amount in descending order.

Specify SQL command

Click on the Parameters and do the variable mapping, as shown below.

  • Parameters: Parameter 0
  • Variables: User::QueryDetails
  • Param direction: input

Parameter mappings

Add the flat file destination, as shown below.

Add the flat file destination

In the flat file connect manager editor, add a new flat file, but it should point to the same text file we used earlier for output. The second data flow task should write in the same flat-file as per our requirement.

flat file connect manager editor

In the mappings, verify the columns from SQL query and flat file output.

Verify input output mappings

Click Ok, and we see the following SSIS package configuration.

package configuration

You should also uncheck option – Overwrite data in the file from both of the flat files, as shown below. If we enabled this option, it overwrites data in the flat file in each loop.

Overwrite data in the file

Understanding SSIS package logic used in this article

Click Ok, and we see the following SSIS package. Before executing this package, let me walk through you the complete logic we implemented in this article.

  1. First, it runs the execute SQL task (in this case, Get Distinct [ModelRegion]) and gets unique values for the [ModelRegion] in the full Result set variable
  2. Next, it runs a Foreach loop container for each unique value
    1. For the first unique value, it executes the first data flow task. In this data flow task, we print the [ModelRegion] value in the flat file
    2. Then it goes to the second data flow task and prints the values corresponding to a [ModelRegion] for which loop is executing
  3. It continues the execution of the package for remaining values and completes once it writes all values in the flat file

Understand logic

Execute the SSIS package using the Start button in the menu bar. It runs the package successfully, as shown below. In case of any error, we get a red cross icon on the respective failed task.

Successful package

Go to the destination directory, which we specified in the flat file connection, and view the records. It satisfies our initial requirements.

Final output

Conclusion

In this article, we explored how SSIS packages help to generate composite outputs as per our requirements. It helps us avoid manual tasks and automate things without writing any complicated code. It is useful for both database administrators and developers.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SSIS Foreach Loop vs For Loop Container
  2. Using a CHECKPOINT in SSIS packages to restart package execution
  3. SSIS Flat Files vs Raw Files
  4. Overview of SSIS Package Logging
  5. Using Biml scripts to generate SSIS packages
168 Views