CODEWITHSUNDEEP
c#excelssis
d_anass
d_anass

Reputation: 131

Data Flow Task with variable source

In an SSIS package I need to put the content of 2 SQL queries in 2 sheets of an Excel file. The results of the 2 queries are in 2 object variables, I'm wondering if I can use a Data Flow Task for this. Issues :

  1. I don't know what columns I will have (number of columns, names of columns), the query is created at the moment of the execution Set @qry = 'Select ...' EXEC(@qry)
  2. I have the same problem with the Excel file, I can't have a precise Template, all I know about the Excel File is that we'll have 2 sheets. Is this possible? If not, is there another way (Script task, SSRS...)?

Upvotes: 0

Views: 198

Answers (1)

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

As a general answer, no, you can't do this with SSIS. Since you tagged this with C# however, you can use OLE to add sheets to an Excel file and add data to those sheets, http://jayadevjyothi.blogspot.com/2013/01/step-1-create-new-project.html . This can be done outside of SSIS, or if your solution needs to run inside SSIS, you can put the C# inside a script task.

           // Excel file path
        string excelFilePath = @"F:\Excel File.xlsx";
        // Connection string for accessing excel file
        string connectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=""Excel 12.0 Xml;HDR=YES""", excelFilePath);

        using (OleDbConnection Connection = new OleDbConnection(connectionString))
        {
            try
            {
                Connection.Open();
                using (OleDbCommand Command = new OleDbCommand())
                {
                    Command.Connection = Connection;
                    Command.CommandText = "CREATE TABLE [Students] ([First Name] Char(200), [Last Name] Char(200), [Age] Char(2))";
                    Command.ExecuteNonQuery();
                    Console.WriteLine("Table Created Successfully");
                }
                Console.ReadLine();
            }
            catch (OleDbException ex)
            {
                Console.WriteLine("Message: " + ex.Message);
                Console.ReadLine();
            }
            finally
            {
                Connection.Close();
            }
        }

Upvotes: 2

Related Questions