Vbscript to Read for a File in a Directory for Ssis

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

Trouble

At that place are several techniques bachelor to import and export information for SQL Server. In addition, at that place are free SQL tools and utilities available that might be helpful for specific use cases. In SQL Server Integration Services (SSIS), we can utilize a Flat File Source to load Text/CSV files. Despite this, I would similar to demonstrate how to import Text/CSV files using the Script Job.

Solution

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

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

There are many use cases to meet business organization needs using Script Tasks, such every bit:

• Using the Script Task, we can use variables; for example, nosotros can pass variable values between SSIS packages
• Nosotros can easily become data from Active Directory using a Script Task.
• We can read/write files similar text, Excel, etc.
• When working with images, the Script Task tin be helpful.
• Using the Script Task, nosotros can import/consign data from the database engine to the file system and vice versa.
• We can perform tasks using a Script Job like logging, raising errors, warnings or advisory messages.
• Nosotros 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 but installed SSDT (SQL Server Data Tools) which installs a shell of Visual Studio which can execute a Script Chore.

How to configure a Script Task in SSIS

In order to configure the SQL Server Integration Services package for a Script Job, nosotros demand to understand the post-obit:

• Specify the custom script to encounter concern needs.
• Choose the method in the VSTA projection which is Integration Services runtime calls.
• Specify the language for script.
• Specify readonly/writeonly variables for utilise in the script.

Here are the steps.

Step one: Sample SQL Server Database Script

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

Utilize Master  GO   Create Database SSIS_FileData  Get   USE SSIS_FileData  Become   CREATE TABLE Customer_Data  (    Proper noun Nvarchar(400),    city nvarchar(200),    Accost nvarchar(1000)  )  GO          

Pace ii: Gear up Source files for reading

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

These files contain the below information.

"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 information technology is possible to increase the number of files in the folder.

Step 3: Configure Project

Open up Visual Studio and execute these steps:

In the menus select, File > New > Project.

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

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 beneath.

Step 4: Configure ADO.Internet Connection to Import Text/CSV Information into Table

In order to import information into the table, I need a SQL Connection in the packet. There are ii ways to create a connection managing director in SSIS: Package Level and Projection Level.

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

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

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

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

Footstep 5: Configure SSIS Variable

Create Variable: FilePath

In social club to become the files from the directory, I needed the source folder path. For the purpose of setting a dynamic path, I am going to apply a variable to practise this. Correct click in the Control Menses and select the Variables tab. Now click on Add Variable and assign the Name, Data Type and Value respectively.

Create Variable: TableName

I am going to add a variable TableName and configure it every bit shown beneath.

Footstep 6: Configure Script task in SSIS

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

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

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

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

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

I put a Try-Catch block for exception treatment in the Script Task. In improver, I used an error raising result FireError in the catch block.

Now I am going to write the code as shown below and I volition explain the code likewise.

public void Chief() {    endeavor    {       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) equally SqlConnection);       //Read data from table or view to data table                              foreach (string fileName in fileEntries)       {           System.IO.StreamReader SourceFile = new Organisation.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.Supervene upon(",", "','").Replace("   ", "','") + "')";              SqlCommand SQLCommand = new SqlCommand(query, conn);              SQLCommand.ExecuteNonQuery();              ////MessageBox.Testify(query);          }          ctr++;           }       }          Dts.TaskResult = (int)ScriptResults.Success;       conn.Close();    }    catch (Exception ex)    {       Dts.Events.FireError(0, "Exception from Script Job", 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.

cord Line = cord.Empty;          

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

string query = cord.Empty;          

Configure a SQL Connection in Script Task

I used the following lawmaking to use the "DBConnection" for the connection that nosotros setup in step iv.

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

Process to Loop Through Files and Load to Tabular array

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

Execute SSIS Parcel

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

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

Conclusion

• To check the lawmaking that is generated I used a MessageBox in the Script Chore which is commented out. Annotation, yous can't use a MessageBox in a production environs considering the package waits while the MessageBox is open.
• I used a global SQL Connectedness in the Script Task, but y'all can likewise create your ain SQL Connectedness in the Script Job if needed.
• I hardcoded the database name in the Script Task, but this can also be configured to pull from a variable.

Adjacent Steps

• Kindly run in the test server before rolling out in product.
• 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 than virtually the Script Task in SQL Server Integration Services.

Related Articles

Popular Articles

About the writer

Bhavesh Patel is a SQL Server database professional with 10+ years of feel.

View all my tips

Commodity Last Updated: 2019-08-30

sonnieroveregic.blogspot.com

Source: https://www.mssqltips.com/sqlservertip/6149/import-text-and-csv-files-into-sql-server-database-with-ssis-script-task/

Share this post