Excel file loading in SSIS

Hi All,

We did the development of SSIS packges on 32 bit machine. We have few excel files which is loaded using SSIS.

Now the same was deployed to anothe rmachine(64 bit). This 64-bit machine does not have Microsoft office installed.

And all packages(which loads excel files) failed. Hence can someone answer my following questions:-

1) In order to load excel files using SSIS, is it necessary that Microsoft excel software should be installed on that machine?

2) If answer to above is yes, Can Microsoft excel viewer be used instead of Microsoft office(excel)?

Thanks

Sid

Hi,

I am not an expert in SSIS but i have tried importing excel source data to sql using ssis package in a machine that does not have MS Office installed, but it worked for me.

Thanks,

OM$.

|||

Thanks for reply: Here is my problem:

I run those SSIS packages (which loads excel file) by double clicking it or running it through SSIS editor. It works great.

However if I run it using the command line dtexec.exe then following error arises: -

Error: 2007-08-03 16:53:27.42
Code: 0xC0202009
Source: PkgExtract Connection manager "SRC_Connection"
Description: An OLE DB error has occurred. Error code: 0x80040154.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
Error: 2007-08-03 16:53:27.42
Code: 0xC020801C
Source: Data Flow Task - Extract Data Excel Source [1860]
Description: The AcquireConnection method call to the connection manager "SRC_Connection" failed with error code 0xC0202009.
End Error
Error: 2007-08-03 16:53:27.43
Code: 0xC0047017
Source: Data Flow Task - Extract Data DTS.Pipeline
Description: component "Excel Source" (1860) failed validation and returned error code 0xC020801C.
End Error

|||

See your other post on this.

There is no 64-bit driver for Excel. You need to run the 32-bit version of DTEXEC (located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn).

|||

Thanks a ton. That was the solution and works perfectly fine

excel data mining add-in for categorical data

Thank you very much for your previous answers conserning clustering algorithm.

I change a bit my data and now I can get two and three clusters as I wish.

But now I have a different question. I wish to keep my Structure and Model for further trials with different algorithm values, like cardinality. I tried the following

I selected in excel Data Mining and then the option cluster button.

Then in the cluster wizard I chose analysis service data source and I reform the query as I need adding only at the end

where "STAFF_YES" = 1. Then I choose the needed columns and target value. Finally I select browse model, enable drill through only and I finish the wizard and get some results, rather good. But i need to try again with different parameters.

I tried Business intelligent Studio to check my Analysis service. The structure were here but when I changed any parameter and I tried to reprocess I got the following messages

Processing Mining Structure 'try Structure_1' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension 'try Structure_1 ~MC-__RowIndex' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute '(All)' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_GOOD' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Processing Dimension Attribute 'EPI_CELL_SINO_LOOSE' completed successfully.
Start time: 5/8/2007 4:55:08 μμ; End time: 5/8/2007 4:55:08 μμ; Duration: 0:00:00
Errors and Warnings from Response
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_LOOSE' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
Internal error: An unexpected error occurred (file 'pcprocbinding.cpp', line 6645, function 'PCDBProcBinding:electOrChangeCartridge').
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'try Structure_1 ~MC-__RowIndex', Name of 'try Structure_1 ~MC-__RowIndex' was being processed.
Errors in the OLAP storage engine: An error occurred while the 'EPI_CELL_SINO_GOOD' attribute of the 'try Structure_1 ~MC-__RowIndex' dimension from the 'DMAddinsDB2' database was being processed.

And no change to the structure take place.

Please help me if you can

Thank you in advnce

Best regards.

Manolis

We discovered this issue and it will be addressed in a future release but currently there is no workaround for this problem using BI Dev Studio.

Here is an alternate way of doing the same thing:

- in Excel, under the Data mining tab, select the "Advanced\Add model to mining structure" option

- create a new model using whatever algorithm (Clustering, I assume) and setting any parameters you might want.

The wizard will create a new model inside the same structure and process it.

If having multiple models does not bother your, you can leave them all inside the structure. Otherwise, you can delete the old models using the Manage Models option in the Data Mining tab.

Also, after executing the steps you mentioned in the BI Dev Studio, chances are that your mining structure is in an incosistent state. You can restore its original (processed) state by invoking, under Manage Models, Process structure with new data then point to the data source you used in creating the structure the first time

Hope this helps

sql

exceeding max nesting level of 32

Is there a way to reconfigure the max nesting level of 32 to something a tad bit higher. Using a cursor and in one case when this stored procedure gets called it exceeds the max nesting level of 32. Will have to rewrite but for now was wondering if that was a setting that could be reconfigured.

THANK GOODNESS NO!

Nesting more than 32 levels signifies a very bad conceptual and/or logical design.

SQL Server is designed for SET based operations. CURSOR (row-wise) operations are rarely necessary. If you were to post your code, and an explanition of the task, we might be able to help you redesign the process so that both the nesting limit can become a 'non-issue' AND the entire process can execute orders of magnitude faster.

|||I know it is bad design. This function has been a good solution till recently, we are absorbin another system that is causing the problem,and we are under extreme deadlines. Here is the code. These tasks are a parent child relation, a task can have child tasks etc

ALTER FUNCTION [dbo].[fncTaskTree]
(
@.prmTask varchar(30),
@.prmParentTask varchar(30) = '',
@.prmProcessOrder int = 0,
@.prmDepth int = 0,
@.prmSortLevel varchar(128) = '1'
)

RETURNS @.TaskTree TABLE
(
ParentTask varchar(30),
Task varchar(30),
ProcessOrder int,
Depth int,
SortLevel varchar(128),
ProcessId uniqueidentifier
)

AS
BEGIN

/* Title: fncTaskTree
Parameters: @.prmTask - Task that was entered in the Task field parameter
@.prmParentTask - Task that was selected from the Task List parameter
@.prmProcessOrder -
@.prmDepth -
@.prmSortLevel -

Returns: @.TaskTree - Table of Task Tree
Author: Dale Thompson
Created: 08/31/2004
Purpose: Recursive function to populate TaskTree Table

Update History
--
*/

Declare @.intSortLevel int
Declare @.strSortLevel varchar(128)

SET @.prmDepth = @.prmDepth + 1
SET @.intSortLevel = 0

-- Insert the Process that the function is called with into the results table

INSERT INTO @.TaskTree
SELECT @.prmParentTask, Process, @.prmProcessOrder, @.prmDepth, @.prmSortLevel, ProcessId
FROM Processes
WHERE Process = @.prmTask

-- Create cursor and loop through the children for the passed in Task

Declare @.wsTask varchar(30)
Declare @.wsParentTask varchar(30)
Declare @.wsProcessOrder int
Declare @.wsProcessId uniqueidentifier

Declare curTasks Cursor static For
Select
ProcTasks.Process,
Task,
ProcessOrder,
ProcessId
From ProcTasks
Inner Join Processes
On ProcTasks.Process = Processes.Process
Where Task = @.prmTask
Order By
ProcessOrder,
ProcTasks.SubOrder,
ProcTasks.Process

Open curTasks
Fetch Next from curTasks into @.wsTask, @.wsParentTask, @.wsProcessOrder, @.wsProcessId
While @.@.Fetch_Status = 0
BEGIN

SET @.intSortLevel = @.intSortLevel + 1
SET @.strSortLevel = @.prmSortLevel + '-' + str(@.intSortLevel,3)
INSERT INTO @.TaskTree
SELECT * FROM fncTaskTree(@.wsTask, @.wsParentTask, @.wsProcessOrder, @.prmDepth, @.strSortLevel)

Fetch Next from curTasks into @.wsTask, @.wsParentTask, @.wsProcessOrder, @.wsProcessId

END

Close curTasks
Deallocate curTasks

Return

End
|||

Katie,

I'm going to move this thread to the Transact-SQL forum where there will be more heads to work on your problem...

|||

Katie:

Are you using SQL 2000 or SQL 2005?