SSIS - Load File's information to SQL Server Table

Scenario:

You are working as SQL Server Integration Services(SSIS) developer, You need to create an SSIS Package that should be able to load file's information form a folder to SQL Server table. The SSIS Package should load file name, file created date-time and file size in kilo bytes.

Solution:

First of all we need to create a table in which we would like to save file's information from a folder.

use YourDatabaseName
go
CREATE TABLE dbo.FileInformation
(
Filename VARCHAR(100),
FileCreatedDateTime DATETIME,
FileSize INT
)

Step 1:
Open BIDS or SSDT and create new SSIS Package, In your SSIS Package, create a variable VarDirectoryPath of string type. Provide value to this variable C:\MyTestFolder as you would like to create the file's information from this folder. You will be providing the path of folder in which you have the files.
Create variable in SSIS Package to load file's information to SQL Server Table


Step 2:
Create an ADO.NET Connection to your Database and rename to DBConn.
Create an ADO.NET connection in SSIS Package to use in Script Task to load File's information to SQL Server Table

Step3: 
Drag Script task to Control Flow Pane and add  VarDirectortyPath as input variable as shown below
Add user variable to Script Task in SSIS Package to load file's information to SQL Server Table



Step 4: 
Click on Edit Script Button and then copy below code to your Script Task Editor.

Under #Region NameSpaces, paste below namespaces.

using System.IO;
using System.Data.SqlClient;


Got to public void Main()
{
and paste below code.

SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
//MessageBox.Show(myADONETConnection.ConnectionString, "ADO.NET Connection");

string DirPath = Dts.Variables["User::VarDirectoryPath"].Value.ToString();
//get all files from directory
string[] files = Directory.GetFiles(DirPath);
SqlCommand sqlCmd= new SqlCommand();
sqlCmd.Connection = myADONETConnection;

//loop through files
foreach (string filename in files)
{

FileInfo file = new FileInfo(filename);

sqlCmd.CommandText = "Insert into dbo.FileInformation Values('" +
 file.Name + "','" + file.CreationTime + "','" + file.Length/1024 + "')";
//MessageBox.Show(sqlCmd.CommandText);

sqlCmd.ExecuteNonQuery();
}

Dts.TaskResult = (int)ScriptResults.Success;
}


Step 5:

Save the script and close the script task editor window. Run your SSIS Package, it should load the file's information from folder to Test.dbo.FileInformation table.

8 comments:

  1. This is a nice article..
    Its very easy to understand ..
    And this article is using to learn something about it..

    c#, dot.net, php tutorial

    Thanks a lot..!

    ReplyDelete
  2. I sent you an email about sqlconnection in the c# script

    ReplyDelete
  3. One imperative manner by which information technology is influencing work is by diminishing the significance of separation. bezoek website

    ReplyDelete
  4. Of course, you can get a customary 800 number from the telephone organization, yet how well is it getting down to business for you? https://callgear.com/product/phone-numbers/

    ReplyDelete
  5. Correspondingly, they outline a couple of things that they hate too. top reason to know why your business needs customized software

    ReplyDelete
  6. Tracking the drone operator! Since the URBAN DYNAMITE® RF Drone Detector detects both the drone (by its downlink data signals) and its corresponding remote control, the movement of both can be tracked immediately. Drone Jammer

    ReplyDelete
  7. In order to make this law stand, it is the committee’s job to contact the manufacturers of these cellular intercept systems before selling them to local and state law enforcement agencies and they must notify the FBI first. CUAS System

    ReplyDelete