CODEWITHSUNDEEP
excelssis
Philip Herman
Philip Herman

Reputation: 117

SSIS variables that are populated from Excel sheet and aggregate data

I have an Excel sheet with data from a SQL query. I need to perform some aggregations on the values to get the minimum and maximum years, and the minimum and maximum quarters in the list of fields. I need to calculate the total number of counts for the volumes in each year based on the number of quarters in each year.

 Volume || Start Year ||Start Quarter || End Quarter || End Year
   3          2013           2                4           2016

The difference in quarters between the years will give me the total count of all volumes falling in the selected years.

I am trying to assign variables for Minimum and Maximum years as well as Minimum and Maximum quarters to find the total of volumes. Is it possible to assign an SSIS variable for each item in the Excel sheet and then calculate the number of volumes for the selected years?

Upvotes: 0

Views: 268

Answers (1)

user3662215
user3662215

Reputation: 526

In the SSIS package control flow, you can do all of this with an Execute SQL Task calling a stored procedure. From here there are a couple of approaches that can be taken.

1) Apply the original SQL query that populated the Excel file to populate a temporary table. Perform your aggregations using the temporary table and output them to variables that can be defined in the Result Set of the Execute SQL Task Editor.

2) If applying the original SQL query is not an option and you must read from the Excel file, then you would still apply the recommendations from the first option, except the stored procedure would apply an OPENROWSET command to call your Excel file and the worksheet contained. The command will vary slightly depending on the version of Excel used to create the file. The following example is based on Excel version 2007 and above (.xlsx file extension). In order for this to work you'll need to install on the machine running SSIS an AccessDatabaseEngine.exe driver.

SELECT * INTO #Temp FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                             'Excel 12.0 Xml;HDR=YES;Database=C:\folder\MyExcelFile.xlsx', 'SELECT * FROM [Sheet1$]')

The following link better addresses the specifics in using OPENROWSET. http://blog.learningtree.com/using-openrowset-to-read-excel-worksheets-from-sql-server-part-2-linked-sql-queries/

The following link provides some detail about setting up a stored procedure called by the Execute SQL Task to return the output as defined under the Result Set. SSIS Execute SQL Stored Procedure output parameter type mismatch

Hope this helps.

Upvotes: 1

Related Questions