Calling the SP using the Script task and generating the report in SSIS

 Some time we need to call the SP using the Script task in SSIS.

In this demo we will see how we can call the SP in the script task and generate the CSV report.

We are calling the below SP

DB: AdventureWorks2012

Sp

Create Procedure [dbo].[P_get_emp_details] @year INT As   Begin       Select P.firstname,              P.lastname,              E.jobtitle,              E.gender       From   humanresources.employee E              Inner Join person.person P                      On E.businessentityid = P.businessentityid       Where  Datediff(year, hiredate, Getdate()) >= @year   End;

 Taking the Script task.

And writing the below c# code.

public void Main()         {            // TODO: Add your code here              DataTable dt = new DataTable();             using (SqlConnection conn = new SqlConnection("Data Source=BAGESH-PC\\BAGESHDB;Initial Catalog=AdventureWorks2012;Integrated Security=true"))             {                 SqlCommand sqlComm = new SqlCommand("P_get_emp_details", conn);                 sqlComm.Parameters.AddWithValue("@year", 10);                  sqlComm.CommandType = CommandType.StoredProcedure;                  SqlDataAdapter da = new SqlDataAdapter();                 da.SelectCommand = sqlComm;                 da.Fill(dt);             }              string file_path = "H:\\SSIS1\\Report\\" + "Employee_Detail_Report_ " + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss_fff ") + ".csv ";              using (StreamWriter writer = new StreamWriter(@file_path, true))             {                  writer.WriteLine("*****************Employee Details Report generated on : " + DateTime.Now.ToString() + "**************");                    writer.WriteLine("FirstName,LastName,JobTitle,Gender");                  string s = null;                 s = dt.Rows.Count.ToString();                 foreach (DataRow row in dt.Rows)                 {                     string FirstName;                     string LastName;                     string JobTitle;                     string Gender;                     object[] array = row.ItemArray;                       FirstName = array[0].ToString();                     LastName = array[1].ToString();                     JobTitle = array[2].ToString();                     Gender = array[3].ToString();                     writer.WriteLine(FirstName + "," + LastName + "," + JobTitle + "," + Gender);                 }                   writer.WriteLine("Number of records written in this file :  " + s);                 writer.WriteLine("***************** End of the File *****************");             }                Dts.TaskResult = (int)ScriptResults.Success;         }

 Now running the Package.

                    

See the report.   

Report data.

  

Report generated successfully.