Write the data into the custom text file in SSIS

Hello friends, I am good hope you are also doing good. My friend Balaji working in the SSIS and he get the requirement to pull the data from the table and write it into the text file and after that we need to delete the records from the table.

The text file has the naming like Database_Name_Table_Name_Date_Time. This file has custom information like header, footer, and other information. The log file looks like below.    

   

Let’s see the demo

I am reading the data from the table and storing this data into the object variable.

Taking execute SQL task

                     

Now I am creating the connection manager to connect the SQL server database

       

My SQL query is returning the multiple records so I am setting the Full result set and this result set I am storing into the object variable.

Mapping the result set with the object variable.

             

Now I am creating some variables which are used in the script task.

             

Now taking script task and passing these variables into it.

In the script task, I am writing the below code to create t custom text file and write the data into it.

public void Main()                                 {                       string file_path = Dts.Variables["User::File_path"].Value.ToString() + Dts.Variables["User::Database_name"].Value.ToString()                 +"_"+ Dts.Variables["User::Table_name"].Value.ToString()+"_"+ DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff")+".txt";                       using (StreamWriter writer = new StreamWriter(@file_path, true))             {                 writer.WriteLine("*****************Log generated on : "+ DateTime.Now.ToString() +"**************");                 writer.WriteLine(" Package Name :  " + Dts.Variables["System::PackageName"].Value.ToString());                 writer.WriteLine(" User Name :  " + Dts.Variables["System::MachineName"].Value.ToString());                 writer.WriteLine(" Database Name : " + Dts.Variables["User::Database_name"].Value.ToString());                 writer.WriteLine(" Table Name : " + Dts.Variables["User::Table_name"].Value.ToString());                 writer.WriteLine("***************** Start writing data into the file *****************");                 writer.WriteLine("ID\tProduct_Category\tSub_Category_Name\tProduct_Name");                 OleDbDataAdapter A = new OleDbDataAdapter();                 System.Data.DataTable dt = new System.Data.DataTable();                 A.Fill(dt, Dts.Variables["User::Var_dataset_obj"].Value);                 string s = null;                 s = dt.Rows.Count.ToString();                 foreach (DataRow row in dt.Rows)                 {                     string ID;                     string Product_Category;                     string Product_Sub_Category_Name;                     string Product_Name;                     object[] array = row.ItemArray;                     ID = array[0].ToString();                     Product_Category = array[1].ToString();                     Product_Sub_Category_Name = array[2].ToString();                     Product_Name = array[3].ToString();                     writer.WriteLine(ID +"\t\t"+Product_Category+"\t\t"+Product_Sub_Category_Name +"\t\t"+Product_Name);                 }                 writer.WriteLine("Number of records written in this file :  " +s);                 writer.WriteLine("***************** End of the File *****************");             }             Dts.TaskResult = (int)ScriptResults.Success;                                 }

Complete the code.

Now we need to take the execute SQL task to truncate the table.

   

The package is ready to run

   

Before running this package see the records in the table

 

And no file on the location

   

Now running this package

   

Package executed successfully.

Records in the table

           

See the file in the folder

 

Data in this file

We get the expected result.

Balaji hopes this will be helpful for you. Provide your feedback.

Thanks!