How can I pass multiple stored procedures to my script task C# code in SSIS and generate output files?

Question

How can I pass multiple stored procedures to my Scripttask C# code in SSIS and generate output files?

I am successfully able to run one stored procedure from SQL Server database and output one file.

public void Main()
{
    string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");

    try
    {
            string FileNamePart = Dts.Variables["User::FlatFileNamePart"].Value.ToString();
            string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
            string StoredProcedureFig1_1 = Dts.Variables["User::StoredProcedureFig1_1"].Value.ToString();
            string StoredProcedureFig1_2 = Dts.Variables["User::StoredProcedureFig1_2"].Value.ToString();
            string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
            string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();

            // USE ADO.NET Connection from SSIS Package to get data from table
            SqlConnection myADONETConnection = new SqlConnection();
            myADONETConnection = (SqlConnection)(Dts.Connections["localhost.onramps_tacc"].AcquireConnection(Dts.Transaction) as SqlConnection);

            // Execute stored procedure and save results in data table
            string query1 = "EXEC " + StoredProcedureFig1_1;

            // how to run below query too? Stackoverflow question                 
            string query2 = "EXEC " + StoredProcedureFig1_2;

            SqlCommand cmd = new SqlCommand(query1, myADONETConnection);
            DataTable d_table = new DataTable();

            d_table.Load(cmd.ExecuteReader());

            myADONETConnection.Close();

            string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + datetime + FileExtension;

            StreamWriter sw = null;
            sw = new StreamWriter(FileFullPath, false);

            // Write the Header Row to File
            String var = d_table + "i";
            int ColumnCount = var.Columns.Count;

            for (int ic = 0; ic < ColumnCount; ic++)
            {
                    sw.Write(d_table.Columns[ic]);

                    if (ic < ColumnCount - 1)
                    {
                        sw.Write(FileDelimiter);
                    }
            }

            sw.Write(sw.NewLine);

            // Write all rows to the file
            foreach (DataRow dr in d_table.Rows)
            {
                    for (int ir = 0; ir < ColumnCount; ir++)
                    {
                        if (!Convert.IsDBNull(dr[ir]))
                        {
                            sw.Write(dr[ir].ToString());
                        }

                        if (ir < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }

                    sw.Write(sw.NewLine);
            }

            sw.Close();

            Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception exception)
    {
        // Create Log File for Errors
        using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +
                "ErrorLog_" + datetime + ".log"))
        {
            sw.WriteLine(exception.ToString());
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    } 

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

Actual:

one stored procedure as input and output the columns and rows in one O/P file OR error file.

Expected:

Accept multiple stored procedures and corresponding generate OutputFiles and error files

Answer 1

Solution:

public void Main()
        {
            // TODO: Add your code here

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
                string FileNamePart = Dts.Variables["User::FlatFileNamePart"].Value.ToString();
                string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
                string StoredProcedureFig1_1 = Dts.Variables["User::StoredProcedureFig1_1"].Value.ToString();
                string StoredProcedureFig1_2 = Dts.Variables["User::StoredProcedureFig1_2"].Value.ToString();
                string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
                string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();



                //USE ADO.NET Connection from SSIS Package to get data from table
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["localhost.onramps_tacc"].AcquireConnection(Dts.Transaction)
                    as SqlConnection);



                //Execute Stored Procedure and save results in data table
                string query1 = "EXEC " + StoredProcedureFig1_1;
                string query2 = "EXEC " + StoredProcedureFig1_2;
                SqlCommand cmd1 = new SqlCommand(query1, myADONETConnection);
                SqlCommand cmd2 = new SqlCommand(query2, myADONETConnection);
                DataSet dset = new DataSet();
                DataTable d_table1 = dset.Tables.Add("Fig1_1");
                DataTable d_table2 = dset.Tables.Add("Fig1_2");
                d_table1.Load(cmd1.ExecuteReader());
                d_table2.Load(cmd2.ExecuteReader());
                myADONETConnection.Close();


                foreach (DataTable table in dset.Tables)
                {
                    string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + table + datetime + FileExtension;

                    StreamWriter sw = null;
                    sw = new StreamWriter(FileFullPath, false);

                    // Write the Header Row to File

                    int ColumnCount = table.Columns.Count;
                    for (int ic = 0; ic < ColumnCount; ic++)
                    {
                        sw.Write(table.Columns[ic]);
                        if (ic < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);

                        }
                    }
                    sw.Write(sw.NewLine);
                    // Write All Rows to the File
                    foreach (DataRow dr in table.Rows)
                    {
                        for (int ir = 0; ir < ColumnCount; ir++)
                        {
                            if (!Convert.IsDBNull(dr[ir]))
                            {
                                sw.Write(dr[ir].ToString());

                            }
                            if (ir < ColumnCount - 1)
                            {
                                sw.Write(FileDelimiter);

                            }
                        }
                        sw.Write(sw.NewLine);



                    }
                    sw.Close();

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