Excel file as data source

Hi everyone!

I am trying to import data into my sqlserver 2005 database from an Excel 2000 file. The database is empty. I am using the worksheets from the file to create the tables and copy the rows. I am getting follwing errors:

- Pre-execute (Error)

Messages

Error 0xc0202009: {674E15E4-102E-4935-90A2-8B1FFFEFB11D}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)

Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Source 64 - vw_TempOrderDetails" (5280) failed the pre-execute phase and returned error code 0xC020801C.
(SQL Server Import and Export Wizard)

Any suggestion is most welcome.

Regards

Have you read this:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=160340&SiteID=1 ?|||

Hi Mike,

Many thanks for the reply. I will check out this thread and come back.

Regards

|||

Hi!

I went throught the thread but still am unable to figure it out. Will search elsewhere and revert back.

Regards

|||

Though it looks the Import Export Wizard is not working properly ... you can use one of the alternative method .

1Use Distributed Queries2If you donot wantto configure a persistent connectionto the Excel workbookas a linked server, you can import datafor a specific purposeby using theOPENDATASOURCEor theOPENROWSET function. The following code samples also import the datafrom the Excel Customers worksheetinto new SQL Server tables:SELECT *INTO XLImport3FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',3'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]45SELECT *INTO XLImport4FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',6'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])78SELECT *INTO XLImport5FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',9'Excel 8.0;Database=C:\test\xltest.xls','SELECT * FROM [Customers$]')1011

http://support.microsoft.com/kb/321686

Hope this will help you

This is excerpt from msdnlink...

|||

Hi Satya,

Thanks for the reply. I hope this will work. But I will try to work around the alternative mentioned in the subject thread and present the solution.

BTW: I am from New Delhi, India.

Regards,

Venu

sql

Excel Export Limit?

hi,
I'm having issues exporting "large" Reports into Excel.
These reports create 11 separate worksheets with most not going over 7-10K
rows on each sheet.
I have been able to export "smaller" reports that have the same 11 Excel
sheets but perhaps not as as many rows (1-2k a piece).
When I try to export the large reports it ends up toasting the entire RS
site, and i'm not able to get back into RS until it recycles itself 10-15
minutes.
The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
it.
Is this just a RS2K issue? Would upgrading to RS05 fix it?
Thank you for your time
tOn Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
wrote:
> hi,
> I'm having issues exporting "large" Reports into Excel.
> These reports create 11 separate worksheets with most not going over 7-10K
> rows on each sheet.
> I have been able to export "smaller" reports that have the same 11 Excel
> sheets but perhaps not as as many rows (1-2k a piece).
> When I try to export the large reports it ends up toasting the entire RS
> site, and i'm not able to get back into RS until it recycles itself 10-15
> minutes.
> The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
> it.
> Is this just a RS2K issue? Would upgrading to RS05 fix it?
> Thank you for your time
> t
I think the issue is more related to Excel than SSRS. I know that
Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
version. I don't realistically think that the SSRS version will make
much difference. Of course, Excel 2007 supposedly has an unlimited row
capacity. If this is very important and money is not an object, I
would suggest upgrading the OS to Vista, purchase Office 2007 and then
you would need to purchase SQL Server 2005 and install SP2 to run on
Vista. Again, this might be overkill for your situation. That you will
have to decide. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thank you for your response Enrique,
however ... like i said the largest worksheet is less than 10K rows, and it
never even gets to the Excel portion. When i hit export it sits for 5-10
minutes then returns a general IIS error. At that point the RS site is down.
I'm just trying to determine if it's an RS issue, a RAM/Environment issue,
or ...? I know somewhere something is having an issue with the size since
i'm able to export a similar smaller report. Although the smaller report
still ends up taking 10-15 minutes to export.
Not sure where to start looking.
Any other thoughts?
thank you again
t
"EMartinez" wrote:
> On Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
> wrote:
> > hi,
> > I'm having issues exporting "large" Reports into Excel.
> >
> > These reports create 11 separate worksheets with most not going over 7-10K
> > rows on each sheet.
> >
> > I have been able to export "smaller" reports that have the same 11 Excel
> > sheets but perhaps not as as many rows (1-2k a piece).
> >
> > When I try to export the large reports it ends up toasting the entire RS
> > site, and i'm not able to get back into RS until it recycles itself 10-15
> > minutes.
> >
> > The RS server is a Win2K3 with 4GB ram and RS2K is the only thing running on
> > it.
> >
> > Is this just a RS2K issue? Would upgrading to RS05 fix it?
> >
> > Thank you for your time
> > t
>
> I think the issue is more related to Excel than SSRS. I know that
> Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
> version. I don't realistically think that the SSRS version will make
> much difference. Of course, Excel 2007 supposedly has an unlimited row
> capacity. If this is very important and money is not an object, I
> would suggest upgrading the OS to Vista, purchase Office 2007 and then
> you would need to purchase SQL Server 2005 and install SP2 to run on
> Vista. Again, this might be overkill for your situation. That you will
> have to decide. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS 2005 does render Excel better. With 2000 I would have issues when a user
would export a large amount of data to Excel. It would freeze up the server.
At one point to get around this I would provide a link to CSV ascii format
and have them use that instead of exporting to Excel. With RS 2005 I no
longer have to do that. It is extremely rare that I have an issue with the
server now.
I have a user that would export frequently to Excel (and he is the reason I
came up with the CSV workaround). He does not need the workaround any more.
Based on my experience I would say you have a high probability of fixing the
problem if you upgrade. Not only did my server stop freezing but I saw a
pretty significant speedup with pdf and excel export.
Other benefits of upgrading: end user sorting, multi-select parameters, date
picker. I highly recommend upgrading.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DigHazuse" <DigHazuse@.discussions.microsoft.com> wrote in message
news:2E4EF2CD-5DCD-40D2-BD99-FDA5BD5CFCF0@.microsoft.com...
> Thank you for your response Enrique,
> however ... like i said the largest worksheet is less than 10K rows, and
> it
> never even gets to the Excel portion. When i hit export it sits for 5-10
> minutes then returns a general IIS error. At that point the RS site is
> down.
> I'm just trying to determine if it's an RS issue, a RAM/Environment issue,
> or ...? I know somewhere something is having an issue with the size since
> i'm able to export a similar smaller report. Although the smaller report
> still ends up taking 10-15 minutes to export.
> Not sure where to start looking.
> Any other thoughts?
> thank you again
> t
>
> "EMartinez" wrote:
>> On Jun 25, 5:19 pm, DigHazuse <DigHaz...@.discussions.microsoft.com>
>> wrote:
>> > hi,
>> > I'm having issues exporting "large" Reports into Excel.
>> >
>> > These reports create 11 separate worksheets with most not going over
>> > 7-10K
>> > rows on each sheet.
>> >
>> > I have been able to export "smaller" reports that have the same 11
>> > Excel
>> > sheets but perhaps not as as many rows (1-2k a piece).
>> >
>> > When I try to export the large reports it ends up toasting the entire
>> > RS
>> > site, and i'm not able to get back into RS until it recycles itself
>> > 10-15
>> > minutes.
>> >
>> > The RS server is a Win2K3 with 4GB ram and RS2K is the only thing
>> > running on
>> > it.
>> >
>> > Is this just a RS2K issue? Would upgrading to RS05 fix it?
>> >
>> > Thank you for your time
>> > t
>>
>> I think the issue is more related to Excel than SSRS. I know that
>> Excel 2003 has a 65,000 or so row limit. This is regardless of SSRS
>> version. I don't realistically think that the SSRS version will make
>> much difference. Of course, Excel 2007 supposedly has an unlimited row
>> capacity. If this is very important and money is not an object, I
>> would suggest upgrading the OS to Vista, purchase Office 2007 and then
>> you would need to purchase SQL Server 2005 and install SP2 to run on
>> Vista. Again, this might be overkill for your situation. That you will
>> have to decide. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>

Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

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