Performance Problems Working In BIDS With Excel 2007 Connection
Mar 12, 2008
Hello everybody,
I have some problems of performance in my machine when i try to work whit a ETL that contains one connection of a excel 2007 file. These file has 3 page and it's 19MB. when i try to edit some data flow, my bids work very slow around 3 or 5 minutes, openning the Data flow source.
My machine configuration is:
Processor: Core2Duo of 1.66Ghz
Ram: 2GB
D.D:80GB
I'm working over a DB engine Sql Server 2005 Developer edition.
So, the excel file has thes load:
Page1: 5 columns X 62000 rows
Page2: 14 columns X 62000 rows
Page3: 12 columns X 123000 rows.
If i work with a file with the same structure but few rows, they work better. How can i correct these situation?.
Thank you!!
JULIAN CASTIBLANCO P.
Bogotà , Colombia.
View 3 Replies
ADVERTISEMENT
Aug 10, 2007
Hello everyone,
Since I would like to use an Excel 2007 File (*.xlsx) as Data Source, I created an ODBC Connection. It worked fine so far: the connection is established, I get the data as expected in the "Query Designer tab" and I could insert the fields in my report. However, when I want to see the result on the "Preview tab", I get an error saying that the "Report Definition '/myReport' is not valid...".
I don't know what could be wrong in my Report... Am I missing something in the ODBC configuration?
My Connection String is as follows:
Dsn=pl_excel;dbq=C:PublicPL_DataSource.xlsx;defaultdir=C:Public;driverid=1046;fil=excel 12.0;maxbuffersize=2048;pagetimeout=5
I also tried to create an OLE DB connection but I got the same error...
Thanks for your help, I'm waiting for your suggestions...
View 2 Replies
View Related
Sep 20, 2006
i re-installed the SQLEXPR_TOOLKIT.EXE several times(4) trying to get the BIDS part to work. Each time i had the issue of it looking for the devenev.exe as others have mentioned. I gave up on a 5th install and went for the unsupported option of running vs_setup , after the toolkit.exe dumped all the install files to its temporary directory i went in there and ran it sperately...but essentially i did it the same way as this method...
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=368549&SiteID=1
while this did work, it seems to be the unsupported way.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=665067&SiteID=1
Im not opposed to going back and doing it right, but was there ever a conclusion as to why the BIDS program sometimes doesnt install? im using 2k5 express products on this machine...
View 22 Replies
View Related
Mar 15, 2008
Hi,
I have reinstalled Office 2007 (to changre the license key)
after this, the data mining excel add-in failed to load.
the "COM add-ins" displays: "not loaded. a runtime error occurred during the loading"
reinstalling the add-in doesn't solve the problem
installing the 2008 version don't solve the issue too.
There is no other information, what can I do to solve the issue?
thanks.
View 3 Replies
View Related
Jan 2, 2008
hi,
i have one application in Microsoft excel 2003. but when i open this in Microsoft excel 2007 it gives me error saying that
error inintializing menu,
object variable or with block variable not set.
kindly guide me what i can do for this. i m not getting exactly what is happening.
thanks & regards,
Vishruti.
View 3 Replies
View Related
Jan 16, 2008
Hi Experts,
The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?
Trigger
USE [personnelreq]
GO
/****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/*****************************************************************************************************************************************************************
* Trigger Name: tgrTracking
* Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition
* requisitions when the requisitions are emailed, transferred, or completed.
* History
* TKT/CO # Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 Zsofia Horompoli Created
* CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events
*****************************************************************************************************************************************************************/
CREATE TRIGGER [tgrTracking] ON [dbo].[Log2]
FOR INSERT
AS
DECLARE @reqId int -- store requisition ID
DECLARE @eventId int -- event value inserted
DECLARE @parentId int -- store parent requisition ID
DECLARE @eventTime datetime -- store date/time of event
DECLARE @email varchar(50) -- store email address
DECLARE @logId int -- store ID assigned to the record in the Log2 table
DECLARE @location varchar(30) -- Location
DECLARE @title varchar(30) -- Job title
DECLARE @multiReq int -- > 0 = Multi NSA
DECLARE @origMail varchar(50) -- Originator's email address
DECLARE @subject varchar(200) -- Email subject line
DECLARE @from varchar(50) -- From email
DECLARE @body varchar(8000) -- Email body
DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus)
DECLARE @sReqType varchar(30) -- Requisition type description
DECLARE @appEmail varchar(500) -- Approvers' email addresses
SELECT @from = 'compensationandbenefits@west.com'
DECLARE @inserted_rows AS CURSOR -- hold the inserted rows
SET @inserted_rows = CURSOR FOR
SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows
OPEN @inserted_rows
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Is this an email/transfer/complete/deny event?
IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10)
BEGIN -- Yes, is this a deny event?
IF (@eventId = 0)
BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype in (0,1) and id = @reqId and
(SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2
END
ELSE IF (@eventId = 6)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1,6) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE IF (@eventId = 7 OR @eventId = 10)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE
BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
--Is this a requisition to be tracked?
IF @parentId is not NULL
BEGIN --Yes, is this a transfer event?
IF @eventId = 6
BEGIN --Yes, retrieve information for email
SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype
FROM reqs2 R
LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION
LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE
WHERE R.id = @reqId
-- Get email addresses to use
EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT
--Set requisition type description
IF @reqType = 0
BEGIN --Replacement requisition
SELECT @sReqType = 'Replacement'
END
ELSE IF @reqType = 1 AND @multiReq > 0
BEGIN --Multi-NSA
SELECT @sReqType = 'Multiple Net Staff Addition'
END
ELSE IF @reqType = 1
BEGIN --NSA
SELECT @sReqType = 'Net Staff Addition'
END
ELSE IF @reqType = 6
BEGIN --Bonus
SELECT @sReqType = 'Bonus'
END
--Build subject line
--Do we have a location?
IF @location IS NULL
BEGIN --No, default to Nothing
SELECT @location = ''
END
--Do we have a job title?
IF @title IS NULL
BEGIN --No, default to Nothing
SELECT @title = ''
END
SELECT @subject = @location + ': ' + @sReqType + ': ' + CAST(@reqId AS varchar(15)) + ': ' + @title
--Set email body
IF @reqType = 6 -- If Bonus Req
BEGIN
SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req
BEGIN
SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
--Send email
EXEC usp_SMTPMail @SenderAddress = @from, @RecipientAddress = @origMail, @Subject = @subject, @Body = @body, @Cc = @appEmail
END
-- Call the CalcTrackingHours stored procedure
IF @reqType IN (0,1)
BEGIN
EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId
END
END
END
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
END
--clean up
CLOSE @inserted_rows
DEALLOCATE @inserted_rows
Stored Procedure is
USE [personnelreq]
GO
/****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/
/*****************************************************************************************************************************************************************
* Stored Procedure Name: CalcTrackingHours
* Description: Determines the appropriate tracking level, the number of business hours the requisition
* spent at the current tracking level and inserts this information into the timeLog table
* History
* Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 syszxh Created
*****************************************************************************************************************************************************************/
CREATE procedure [dbo].[CalcTrackingHours]
@event int, -- 4=Emailed,6=Transferred,7=Completed
@reqId int, -- Requisition Id
@emailTo varchar(50), -- Email address requisition was forwarded to
@endDateTime datetime, -- Date/time of event
@logId int -- ID assigned to new record in the Log2 table
as
set nocount on
declare @currentUserType as int -- Current user type
declare @startDateTime as datetime -- Date/time requisition arrived at current step
declare @newUserType as int -- New user type
declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table
declare @newTrackLevel as varchar(5) -- New tracking level
declare @trackEmail as varchar(50) -- Email address at current tracking level
declare @count as int -- counter variable
declare @errorValue as int -- Error flag (1 = start date/time missing)
declare @trackHours as decimal(10,2)
select @errorValue = 0
select @startDateTime = NULL
-- Retrieve last email's user type, date/time stamp, and email address
select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid
from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid
where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc
-- Last email found?
if @startDateTime is NULL
begin
-- No, this is the very first email, retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is new user @C&B?
if @newUserType = 2
begin -- Yes, set new tracking level
select @newTrackLevel = 'cb1'
end
else
begin -- No, set new tracking level
select @newTrackLevel = 'dept'
end
end
else
begin
-- Is this event = email?
if @event = 4
begin
-- Retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo
-- Is the current user the same as the new user?
if @currentUserType = @newUserType and @emailTo = @trackEmail
begin
-- Yes, get latest level
select top 1 @newTrackLevel = trackLevel
from timeLog
where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime
order by dateIn desc
select @currentTrackLevel = @newTrackLevel
end
-- Is the current user type @C&B?
else if @currentUserType <> 2 or @currentUserType is NULL
begin
-- No, retrieve current tracking level
exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output
-- Is the new user type @C&B?
if @newUserType = 2
begin -- Yes, retrieve new tracking level
exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output
end
else -- No, retrieve new tracking level
begin
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
end
end
else
begin
-- Yes, retrieve new tracking level
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
-- Retrieve current tracking level
exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output
end
end
-- Is this event = transfer/completed?
else if @event = 6 or @event = 7 or @event = 10
begin
-- No, any existing entries for exec3?
if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb5
select @currentTrackLevel = 'cb5'
end
-- Any existing entries for exec2?
else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb4
select @currentTrackLevel = 'cb4'
end
-- Any existing entries for exec1?
else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb3
select @currentTrackLevel = 'cb3'
end
-- Any existing entries for acct?
else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb2
select @currentTrackLevel = 'cb2'
end
else
begin
-- Set current tracking level to cb1
select @currentTrackLevel = 'cb1'
end
end
-- Calculate hours worked
if @event <> 10
begin
exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output
-- Update information in the timeLog table
update timeLog
set dateOut = @endDateTime, trackHours = @trackHours
where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31'
end
end
-- Is this event = email?
if @event = 4 and @newTrackLevel is not NULL
begin
-- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31')
end
-- Is this event = reopen?
else if @event = 10 and @trackEmail is not NULL
begin -- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31')
end
endProcedure:
Return @errorValue
View 2 Replies
View Related
Feb 22, 2008
I'm working on a fairly straight forward data transfer package and have found that the package runs dramatically faster when I run the package inside BIDS than with DTExec. When I run the package on the server using debug in BIDS, the job completes 1 million rows in around 6 minutes. When I run DTExec with the same package on the same server it is much slower and the package takes roughly 25 minutes to complete.
I know this sounds crazy and that it's supposed to be the other way around with DTExec running much faster, but I'm stumped as to what could be causing the issue. The machine this is running on is a two processor, dual core CPU with GB of RAM and I'm using terminal server to login and create the package with BIDS on SQL Server 2005 SP2.
The main feature of this package is a Foreach container that uses an ADO record set to loop over a set of values from a control table. There are a large number of iterations so the package loops frequently, but the data flow task is fairly simple and uses an OLEDB source and OLEDB destination to transfer data between two SQL Server 2005 databases.
The package works in either BIDS and DTExec, but I'm really puzzled why it would run so much faster inside BIDS?
Thanks in advance,
-Russ
View 7 Replies
View Related
Jul 31, 2007
Haven't been able to find much information on using openrowset with excel 2007 xlsx files. I've tried the following with no success so far.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=\serverfolderfile.xlsx;HDR=No;IMEX=1',
'SELECT f1 FROM [Raw Keywords$] WHERE f1 IS NOT NULL')
If anyone knows the correct way to do this please tell me.
View 3 Replies
View Related
Feb 21, 2007
hellow all,
i installed SqlServer 2005 sp2 on my server.
now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.
anyway, can i do it without the sp2 or office 2007 installed on my server?
thanks for tour reply!
View 5 Replies
View Related
May 15, 2008
When importing an .xlsx file the number of columns stops at 255. Does anybody know how to get all columns imported?
Tried saving the file as text and that allows me to import all the columns but it would be much easier to do this directly from Excel.
Thanks
View 4 Replies
View Related
Jul 17, 2007
Hello Everyone,
Is there any way in RS2005 to export to Excel 2007 format ?
The reason I'm asking is that we have a report that can potentially have more than 256 columns but for now, there is no way of directly importing to excel 2003 (.xls).
Is microsoft going to come out with an improvement to RS2005 or is there already a way to export to Excel 2007 to bypass the 256 column limit?
Regards,
Joseph
View 1 Replies
View Related
Feb 28, 2008
Hi Folks,
I have got a quick question, Is it possible to use office 2007 in SSIS, If so what would be the driver?
or Could we use JET 4.0 itself?
Any thoughts and help in this regard would be appreciated.
Zulfi.
View 3 Replies
View Related
Jun 12, 2007
Hi Guys,
I am trying to export data from database to excel 2007 file on my machine.
I have downloaded the provider for office 2007 and I have XP with sp2 and SQL server 2005 with SP2.
I havn't installed office 2007 on my machine,but I have a excel file which is created in office 2007.To get acess of read and write i have downloaded office compatibilty pack too,So I can read and write into the file.
Now I am creating one SSIS package to export data into excel file.But I m not able to coause I am getting some errors like
"test connection failed because of an error in intializing provider. Invalid UDl file"
"Test connection is failed because of an error in intializing provider.Not a valid file name".
Please help or suggest how to export data from database to 2007 excel file.
Yogesh V. Desai. | SQLDBA|
View 10 Replies
View Related
Sep 9, 2007
Hi all
I am wondering if there is a solution for our current issue i.e. we can't export the query report from SQL report services into 2007 Excel.
We have no problem with Excel 2003.
Any help/reply would be much appreciated.
Cheers
View 9 Replies
View Related
Aug 10, 2007
Hi,
I wants to import data using DTS package from Excel 2007. I have tried this by using DSN but i'm not getting Excel 2007 types.
Please suggest me how to Connect to Excel 2007 using DTS package to import data.
Thanks!
View 3 Replies
View Related
Mar 14, 2007
I'm creating a small test package that copies a value from an Excel 2007 worksheet into a SQL 2005 database (SP2). When I do an Execute Task, I get the following error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \loripMandEScorecardsSQLQueryExample.xlsx failed with error code 0xC0202009.
However, if I do a "Preview" in the Editor for this same Excel Source task that fails, the data comes up as I would expect. What am I missing?
Thanks in advance!
View 2 Replies
View Related
Apr 3, 2008
Has anybody seen this? Is there a configuration setting or something that needs to be changed?
Working with the development version of SQL 2005 that comes with Visual Studio
Problem: I can't seem to get IS to work with Excel 2007 files. I've tried both BIDS and Import/Export Wizard
I've got the connection set to use ACE
The datasource is: c:BranchList - 20080331.xlsx
the extended properties property is: Excel 12.0;HDR=Yes
Test connection succeeds
Preview succeeds
If I save the Excel file as tab-delimited text, BULK INSERT succeeds
But trying to run the package against Excel I get this error, over & over. I have not yet been able to get SQL2005 to import Excel 2007 data
TITLE: SQL Server Import and Export Wizard
------------------------------
Could not connect source component.
Error 0xc0202009: Source - 'Branches $' [1]: An OLE DB error has occurred. Error code: 0x80004005.
Error 0xc02020e8: Source - 'Branches $' [1]: Opening a rowset for "`'Branches $'`" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
View 5 Replies
View Related
Jul 30, 2007
I am currently developing a report for users who insist on exporting to Excel 2007. When I export to Excel on my PC, the report formatting is fine (I am using Excel 2003), however, font sizing as applied in the report is lost when exported to Excel 2007 (i.e. font size 9 in the report is actually 10 in excel 2007). In Excel 2007, the data which runs onto two lines does not show up correctly - the second line is squashed below the first.
Any ideas on how I could resolve this?
View 1 Replies
View Related
Feb 14, 2008
I am new to reporting services. I need to export more than 64K rows to excel from SSRS Katmai. Please let me know how to do this.
Thanks,
Priya
View 8 Replies
View Related
Jun 26, 2007
I have an FTP task that will only work when running in BIDS.
When trying to run as a package on the server or calling the package from a job, I get the following error in the log file:
OnError,<servername>,<user>,FTP Task,{B2F5BB68-C6F8-4EE5-ABC0-71C3636E3E4A},{B7B41A88-18DD-4AD7-8CDE-9E0C1B74DA02},6/26/2007 12:09:11 PM,6/26/2007 12:09:11 PM,-1073573489,0x,Unable to connect to FTP server using "FTP Connection Manager".
When running in BIDS it is fine.
Any know what is causing this?
View 7 Replies
View Related
May 1, 2007
Hi,
I have an Excel 2007 file which contains values in specific cells like A23, D30 etc.
I want to populate the values in these cells using SSIS packages into individual rows of an SQL table.
How can this be achieved ?
Cheers
Sam
View 1 Replies
View Related
May 22, 2008
We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:
Excel found unreadable Content. Do you wish to repair
When we click yes the following log is shown:
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
And all the pivot tables are converted to plain text.
I have read about this in KB 929766 but this do not apply since KPIs are not used.
The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.
Is there some way to €œsave€? pivot tables from destruction? I can€™t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?
View 18 Replies
View Related
Oct 10, 2007
I install add-in and run fine for a while, then all of sudden I could not see Table Analyze tool and data mining tab at my ribbon. I re-run server configuration and re-connect to DMAddinDB (remote server, but I am the administrator), but data mining add-in and table analyzer still not showing at my ribbon.
Then I uninstall DMAddin, re-install it, go through configuration again, but still the tabs are not showing up.
Any idea you can help me get my add in back?
View 12 Replies
View Related
Jan 18, 2008
Hi,
We have a excel 2007 file with a Pivot Table in it. We would like to use the same as data source (But all the source fields as it comes for Pivot). The key issue is that the data to the pivot is from a connection and points to DB - but there is no access to DB. So the Excel is static to us with a PIVOT in it.
Is there a way to get all the data that is avaialble as a source for pivot.
Ex. The select query has co1,co2....co 11 from xxxxx
whereas the PIVot HAS ONLY cO1, CO2 AS ROW, CO3,CO4 AS COL, CO5 AS VALUE AND CO6,CO7 AS FILTER AND OTHERS MISSED OUT IN PIVOT.
THe SSIS as such provide Excel 2007 access to Access OLE DB driver with Excel 12 setting in advanced tab. But not sure whether we can give the range (or) a way around ot get all the data as available in excel regardless of what is used only in the pivot.
Regards,
kart
View 4 Replies
View Related
Jul 16, 2007
Hello all,
I am in the process up testing an upgrade from XP to Vista, and the only thing that I am running into is that my linked servers for Excel that I defined no longer work.
The spreadsheet that I am trying to open is in the 97-2003 format (not the 2007 format), yet I keep getting the same error message "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST1"
Has anyone successfully created a linked server to an Excel spreadsheet on Vista, and if so, please can you provide some insight into what I am doing wrong.
I tried creating a linked server on an XP box running MS Office 2007, and it worked without any issues.
All comments welcome.
thanks
Steve
View 4 Replies
View Related
Aug 6, 2007
When I connect to an Excel 2007 workbook using the new Microsoft.ACE.Oledb.12.0 provider in SSIS 2005, I notice that any tables that I've created in the worksheet are not recognized in SSIS.
In the OLE DB data source component (using the "Table or View" data access mode) the any table(s) I've created are nowhere to be found in the drop down list. Similarly, when I constuct a simple SQL query on the workbook, such as "SELECT * From MyTable" it returns the error:
Microsoft Office Access Database Engine
Hresult: 0x80004005
Description: "The Microsoft Access database engine could not find the object 'MyTable'. Make sure the object exists and that you spell its name and the path name correctly."
I know I have the name right -- I can use structured references to my named table in the worksheet without any problems.
Does the new ACE provider not support Excel 2007 tables? Am I stuck with using "overlapping" cell references to capture data from tables in my worksheet?
View 5 Replies
View Related
May 7, 2007
When we export data mining output including dates through Reporting Services to an Excel spreadsheet, Excel 2007 subtracts 4 years from the dates. Example, the date 11/16/2006 appears in Excel 2007 as 11/16/2002. How should this be handled? Workaround, bug fix, patch,etc.? Thanks, Sam
View 5 Replies
View Related
Dec 5, 2007
I've SSIS 2005 SP2 and Excel 2007 installed. How come I do not see Excel 2007 on the Excel version list?
Thanks,
Ash
View 7 Replies
View Related
May 17, 2008
When I try to create an Offline cube from Excel 2007 I get the following error message. This used to work but I cannot figure out what to loo for.
Code Snippet
Microsoft OLE DB Provider for Analysis Services 2005 :
OLE DB error: OLE DB or ODBC error: XML for Analysis parser: The 'CreatedTimestamp' read-only element at line 1,
column 38747 (namespace http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was
ignored.; XML for Analysis parser: The 'LastSchemaUpdate' read-only element at line 1, column 38803 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Cubes/Cube/Scripts/MdxScript was
ignored.; XML for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 4554
(namespace http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 17325 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 57387 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 60047 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 62847 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 65497 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 72718 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.; XML
for Analysis parser: The 'CurrentStorageMode' read-only element at line 1006, column 75425 (namespace
http://schemas.microsoft.com/analysisservices/2003/engine) under
Envelope/Body/Execute/Command/Batch/Create/ObjectDefinition/Database/Dimensions/Dimension was ignored.;
Errors in the metadata manager. The attribute hierarchy for the Month attribute cannot be created because a
hierarchy with the same ID or name already exists..
Can somebody advice my on what to look for? Thx!
The attribute hierarchy for the Month attribute cannot be created because a hierarchy with the same ID or name already exists. ----- There is no other Month?
Regards,
Dirk
View 3 Replies
View Related
Aug 27, 2007
Is there a way to create a SQL 2005 x64 Linked server to an Excel (or Access) 2003/2007 file? In SQL 2005 32bit this was possible. Does anyone know of a solution or a work around?
View 3 Replies
View Related
May 12, 2007
Greetings,
I'm having a tough time importing some of my legacy database into sql.
I have a number of dbase (IV) files I need to get into SQL. I have tried building a SSIS package with either an foxpro oledb connection or a jet 4.0 one, none of them work bec. of inconsistencies in the data format in my tables (e.g. date fields, etc).
I have tried to save the .dbfs as excel 2007 files, taking advantage of the larger space that comes with '07. Problem is you can't use the import/export wizard with 2007 for some reason and I haven't been able to create a package with the access 12 oledb as I have read.
I have to get some crucial data out of that old system and into the new one and I can't seem to be able to import them properly.
Any hints on what I should do ? (maybe I'm doing something awfully wrong)
Thank you for taking the time to answer my question,
Val
View 12 Replies
View Related
Dec 8, 2007
Hi all,
Posted - 12/05/2007 : 01:54:18
How to export data from SQL Serevr 2005 Express to Excel 2007 using OPENROWSET command.
I have tried the following code But getting error
sSQL.Format("insert into OPENROWSET('Microsoft.JET.OLEDB.4.0','Excel 8.0;Database=%s;','Select * from [%s$]') select * from [%s]", sExcelPath, sSheetName, sTable);
But getting the following error
The OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)" reported an error
I am using automation object for creating EXcel .I have added the header also.
Any help is appreciated
Thanks
View 5 Replies
View Related
Apr 3, 2008
Hello,
I made a package in SSIS to copy some data from SQL server 2005 SP2 to Excel 2007. The package works fine, but generate errors. If I replace the OLE DB destination for Excel 2007 with a Excel destination for Excel 2003 then they errors don't appear. The problem is that I have to use Excel 2007 because the data contains more than 65000 records. I thought maybe it was to much date, but if I limit the amount of data with top 100 it also generate errors.
The errors are:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Post Execute phase is beginning.
Error: 0xC0047018 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: component "Source Declaratiegegevens uit NZDF op NED_NDFSQL01" (1) failed the post-execute phase and returned error code 0x80004002.
Error: 0xC0047018 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: component "Source Declaratiegegevens uit NZDF op NED_NDFSQL01" (1) failed the post-execute phase and returned error code 0x80004002.
Information: 0x40043009 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: "component "Destination Excel 2007" (142)" wrote 353858 rows.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
I hope someone gots a answer...
Thanks in advantage!
Michaël
View 13 Replies
View Related
