Excel Destination Multiple Worksheets!

Hi ,

I am retrieving some data that contains three or four hundred thousand rows. These rows are supposed to go into an excel file with multiple worksheets, since one Excel worksheet cannot handle rows that are more than 65536. Below is what I need to achieve:

Dynamically create mutiple worksheets. Re-direct data like this i.e. first 64K in first worksheet and next 64K in next worksheet and so on. Dynamically name the work sheet with the start value in that work sheet e.g. OrderNumber or OrderDate.

Can we achieve this directly/indirectly?

Your help will be appreciated.

Thanks,

Paraclete

Search is your friend.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1490423&SiteID=1

|||

Hi,

Thanks for your response. I already gone through this post. It does not explain how can we accomplish this. It just says it can be done in two steps. But the 2nd step which needs the elaboration it does not touch it.

Regards,

Paraclete

|||I'm just adding more information: Microsoft has a KB article on importing text files larger than 65,536 rows into Excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596|||

Hi All,

I tried to implement as described in the post below.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1490423&SiteID=1

What happened first I wrote the results to a comma separated Flat file Destination. Then created a For Loop Container in which defined a Data Flow Task. It started with a Flat File Source to the access the data that was written to it earlier. Then added a Data Conversion Transformation. And finally the Excel Destination. There is a variable that is a result of an expression. Based on that expression I will create the name of the work sheet. When I try to do that, the following error apprears in the execution results tab

[Excel Destination [9]] Error: An OLE DB error has occurred. Error code: 0x80040E37.

[Excel Destination [9]] Error: Opening a rowset for "MySheet2" failed. Check that the object exists in the database.

This may be of interest to note that MySheet1 is already created before execution. And the variable value changed during execution i.e. 2nd iteration. I also tried to add a table using a Script Task but was not successful either. The question boils down to: Whether or not we can create a table whose name comes from a variable if yes then what is the syntax for that.

Below are the settings in Excel Destination Editor Connection Managers Tab

OLE DB Connection Manager = Excel Connection Manager (path and name of the excel file)

Data Access Mode = Table name or View name variable

Variable Name = DestSheetName (this is of string type constructed from expression)

Below is the table definition of "MySheet1". Can any one tell how to create this table using a variable name dynamically, since the value of 'DestSheetName' will change with every iteration of the Foor Loop Container.

CREATE TABLE `MySheet1` (
`EmpID_ID` INTEGER,
`LocID` INTEGER,
`Dept` NVARCHAR(6),
`Date_Time_Hired` NVARCHAR(20),
`RowGroup` INTEGER
)

Shema will remain same of every Table i.e. MySheet2, MySheet3 etc.

Thanks,

Paraclete

|||

You have to explicitly create each worksheet before trying to load it. The way of doing that is using a Execute sql task in the control flow with an create table statement that uses the excel connection manager. I know there are other threads in this forum that explains that. I have a post that shows it; it contains other logic, but hopefully you will get the idea:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement

Thanks,

Paraclete

|||

Did you check the link I provided? from that link:

Add an Execute SQL Task inside of the ForEach Loop Container and create a precedence constraint (yes, a green arrow!) from the Execute SQL Task to the Dataflow task.

Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager in Connection property and write a Create table statement in the SQLStatement as follows:

CREATE TABLE `Excel Destination` (
`HeaderID` INTEGER,
`HeaderName` NVARCHAR(50),
`LineID` INTEGER,
`LineName` NVARCHAR(50),
`LineDetails` NVARCHAR(50)
)

As you can see, the objective of this EST is to create the excel file to make it available to the data flow task on every iteration; and it is here where the power of the expression come handy.

Create a new variable, to hold the file name to be creates. I created a variable called 'ExcelFileName' of String Type (give an intial name in the variable window to avoid validation errors).

Now, use an expression to change the value of the variable at run time; to do so, go to the preperties windows of the Variable and change the porperty EvaluateAsExpression to true and click on the expression property to bring the expression editor. That expression will change the value of the variable as the package iterates through the recordset. I used an expression like:

"C:\\Temp\\Report-"+ (DT_STR, 25,1252)@.[User::HeaderID] +".xls"

In the Excel file connection manager, let's create another expression that uses the 'ExcelFileName' variable to change the ExcelFilePath. To do so, click in the connection manager, go to the properties window and click in the expression property. Once the Property Expression Editor is available; choose ExcelFilePath and bring the expression builder. Write an expression like:

@.[User::ExcelFileName]

|||

Paraclete wrote:

Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement

Thanks,

Paraclete

Sure it can. You would create the statement as an expression-based variable and set the SQLSourceType of the Execute SQL Task to Variable.
|||

Same approach, different words:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=949540&SiteID=1