How To Create Csv File Dynamically In Ssis

By: | Updated: 2019-08-30 | Comments (8) | Related: More > Integration Services Development

Problem

There are several techniques available to import and export data for SQL Server. In addition, there are free SQL tools and utilities available that might be helpful for specific use cases. In SQL Server Integration Services (SSIS), we can use a Flat File Source to load Text/CSV files. Despite this, I would like to demonstrate how to import Text/CSV files using the Script Task.

Solution

Why use a Script Task in SSIS to import text data?

A Script Task is one of the best components which is scalable to use in SSIS. Also, you can add new functionality using C# or VB.

There are many use cases to meet business needs using Script Tasks, such as:

Using the Script Task, we can use variables; for example, we can pass variable values between SSIS packages
We can easily get information from Active Directory using a Script Task.
We can read/write files like text, Excel, etc.
When working with images, the Script Task can be helpful.
Using the Script Task, we can import/export data from the database engine to the file system and vice versa.
We can perform tasks using a Script Task like logging, raising errors, warnings or informational messages.
We can also send HTML messages using a Script Task.

Before using a Script Task, we need to know how to run a script and the computer must have Microsoft Visual Studio Tools for Applications (VSTA) installed. I only installed SSDT (SQL Server Data Tools) which installs a shell of Visual Studio which can execute a Script Task.

How to configure a Script Task in SSIS

In order to configure the SQL Server Integration Services package for a Script Task, we need to understand the following:

Specify the custom script to meet business needs.
Choose the method in the VSTA project which is Integration Services runtime calls.
Specify the language for script.
Specify readonly/writeonly variables for use in the script.

Here are the steps.

Step 1: Sample SQL Server Database Script

The following script creates a database SSIS_FileData and sample table Customer_Data.

Use Master  GO   Create Database SSIS_FileData  GO   USE SSIS_FileData  GO   CREATE TABLE Customer_Data  (    Name Nvarchar(400),    city nvarchar(200),    Address nvarchar(1000)  )  GO

Step 2: Prepare Source files for reading

I have two types of files, CSV and text as shown below.

Source files

These files contain the below data.

Source files format

"NG_Customer" is CSV file which contains comma delimited data and "SG_Customer" is a text file which contains tab delimited data. Currently, in this folder I have only two files, but it is possible to increase the number of files in the folder.

Step 3: Configure Project

Open Visual Studio and execute these steps:

In the menus select, File > New > Project.

I created a new SSIS project and named it "Importing_Data_In_SQL_From_TextFile_Using_ScriptTask".

Creating a project

After creating a project, in the Solution Explorer there is a default package.dtsx. You can right click on package.dtsx and rename as I did and as shown below.

Changing to package name

Step 4: Configure ADO.NET Connection to Import Text/CSV Data into Table

In order to import data into the table, I need a SQL Connection in the package. There are two ways to create a connection manager in SSIS: Package Level and Project Level.

With the Package Level option, we need to create a connection manager in every package within a project and when a connection manager is created at the Project Level then the connection is available for all packages in the project.

I prefer to use Project Level connections. Right click on Connection Managersand click on New Connection Manager and a list of connection manager types will pop up.

initiated globally ADO.NET Connection

I choose ADO.NET (ActiveX Data Objects) and clicked the Add button. The following screen, Configure ADO.NET Connection Manager, opens where you can configure the connection.

Assign Connection Property

In the Connection Managers tab, I renamed the new connection to DBConnection.

Assign name to ADO.NET Connection

Step 5: Configure SSIS Variable

Create Variable: FilePath

In order to get the files from the directory, I needed the source folder path. For the purpose of setting a dynamic path, I am going to use a variable to do this. Right click in the Control Flow and select the Variables tab. Now click on Add Variable and assign the Name, Data Type and Value respectively.

Taking a local variable Filepath to getting a files from directory

Create Variable: TableName

I am going to add a variable TableName and configure it as shown below.

Taking a local variable TableName for dumping the data.

Step 6: Configure Script task in SSIS

Drag a Script Task from the SSIS Toolbox to the Control Flow.

Taking a Script task

Double click on the Script Task and the Script Task Editor will open. Set the ReadOnlyVariables to FilePath, TableName as shown below.

Assigned readonlyvariables to script task

Now I am going to click on Edit Script and enter the code.

Display visual studio script editor to write the code

First, I am going to add two needed Namespaces in Namespaces area.

For the purpose of getting the files from directory, I use namespace System.IO.
For the purpose of importing the data into SQL Server, I use namespace System.Data.sqlclient.

Added Namespaces

I put a Try-Catch block for exception handling in the Script Task. In addition, I used an error raising event FireError in the catch block.

Adding exception Handling block

Now I am going to write the code as shown below and I will explain the code as well.

public void Main() {    try    {       Int32 ctr = 0;       string FilePath = Dts.Variables["User::Filepath"].Value.ToString();       string TableName = Dts.Variables["User::TableName"].Value.ToString();       string[] fileEntries = Directory.GetFiles(FilePath, "*");       string Line = string.Empty;       string query = string.Empty;       SqlConnection conn = new SqlConnection();       conn = (SqlConnection)(Dts.Connections["DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);       //Read data from table or view to data table                              foreach (string fileName in fileEntries)       {           System.IO.StreamReader SourceFile = new System.IO.StreamReader(fileName);           ctr = 0;           while ((Line = SourceFile.ReadLine()) != null)           {          if (ctr != 0)          {              Line = Line.Trim();              query = "Insert into SSIS_FileData.dbo." + TableName + " values('" + Line.Replace(",", "','").Replace("   ", "','") + "')";              SqlCommand SQLCommand = new SqlCommand(query, conn);              SQLCommand.ExecuteNonQuery();              ////MessageBox.Show(query);          }          ctr++;           }       }          Dts.TaskResult = (int)ScriptResults.Success;       conn.Close();    }    catch (Exception ex)    {       Dts.Events.FireError(0, "Exception from Script Task", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);       Dts.TaskResult = (int)ScriptResults.Failure;    } }

Variable Initiated in a Script Task

To ignore the file header when reading the input files, I am using variable ctr.

For setting the input variables values, I used the local variables for the source directory and destination table.

string FilePath = Dts.Variables["User::Filepath"].Value.ToString();  string TableName = Dts.Variables["User::TableName"].Value.ToString();

I created an array for the multiple source files.

string [] fileEntries = Directory.GetFiles(FilePath, "*");

To read the file by line, I used local variable Line.

string Line = string.Empty;

To prepare the query for importing the data to the destination table I used a local variable query.

string query = string.Empty;

Configure a SQL Connection in Script Task

I used the following code to use the "DBConnection" for the connection that we setup in step 4.

SqlConnection conn = new SqlConnection();  conn = (SqlConnection)(Dts.Connections["DBConnection"].AcquireConnection (Dts.Transaction) as SqlConnection);

Process to Loop Through Files and Load to Table

Below are the steps to loop through the files and then load each file.

Foreach iteration to reading the files and code is prepared to dumping files data into SQL Server.

Execute SSIS Package

My package is ready for execution. Now I am going to execute it.

Package Execution

The package executed successfully and here is the data in destination table "Customer_Data" which imported data from both files (Text and CSV) "NG_Customer", "SG_Customer".

After reading the files, getting the data from SQL Server Table.

Conclusion

To check the code that is generated I used a MessageBox in the Script Task which is commented out. Note, you can't use a MessageBox in a production environment because the package waits while the MessageBox is open.
I used a global SQL Connection in the Script Task, but you can also create your own SQL Connection in the Script Task if needed.
I hardcoded the database name in the Script Task, but this can also be configured to pull from a variable.

Next Steps

Kindly run in the test server before rolling out in production.
SQL Server Integration Services (SSIS) Tutorial.
Check out variables in Integration Services.
Download the sample files used to import for this tip.
Download the SSIS project used in this tip.
Read more about the Script Task in SQL Server Integration Services.