Excel connection type in execute sql task in SSIS

In execute sql task we have below connection type.

In this article, we will see the Excel connection type.

In this example, I will read an excel sheet data and get the count of all records using the script task.

The package looks like as below.

Now I am creating excel connection manager and configuring it.

Creating a new connection

             

If you have the header on the excel file then you need to select First row has column name. Click ok.        

SQLSourceType will be direct input.

And in SQL Statement I write the select query

SELECT * FROM [100_CC_Records$]

Note: we need to write sheet name in [] followed by $. Otherwise it will throw below error.

[Execute SQL Task] Error: Executing the query "SELECT * FROM [100_CC_Records]" failed with the following error: "The Microsoft Jet database engine could not find the object '100_CC_Records'.  Make sure the object exists and that you spell its name and the path name correctly.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am using [] followed by $.

One more thing when we are using excel we need to set project property Run64bitRunTime false in debugging.

           

     

Read more about this Click here:

Run64bitRunTime debugging property

http://bageshkumarbagi-msbi.blogspot.com/2018/03/run64bitruntime-debugging-property.html

Creating an object variable to store the result set.

      

     

Now assigning this result set

                         

           

Click ok.

I am taking the script task to get the count of all record in the existing excel sheet.

     

       

Double click on the script task.

                       

     

Click on the edit script

To get the count I write below c# code.

public void Main()               {             // TODO: Add your code here             //Object A = Dts.Variables["User::Result_Set1"].Value;             DataTable dt = new DataTable();             OleDbDataAdapter adapter = new OleDbDataAdapter();             adapter.Fill(dt, Dts.Variables["User::Result_Set2"].Value);             MessageBox.Show(dt.Rows.Count.ToString());                      Dts.TaskResult = (int)ScriptResults.Success;               }

Compile, save and close the script editor and click ok.

The package I ready to execute

        

         

See the records in the excel sheet.       

Get the expected result.