Importing Excel saved as XML

Hi guys!

I'm having troubles trying to import an excel file saved as XML.

Tried XML Source, Excel Source, OLEDB Source with no success.

Could someone point me to the right direction?

Thanks in advance.

I cannot see how you would do it without a custom script component as a source which opens the file with Excel automation (Not ideal).

You could (maybe) create your own XSD to allow SSIS to understand the document and create proper column / rows for you but you might end up doing a lot for nothing.

Can you not get it as a normal XLS file?

Importing Excel file using DTS use of VB package

Dear All,
I had created a Package for importing a excel file.
while i created the package the data was ported.
and i had saved the package.
now when i try to port the data by executing the package.
the file is not ported.
what could be the problem.
help me......
Regards _PremAny errors?
Is the file empty, are you trying to import the same file?
Instead of DTS you can also use OPENROWSET or OPENDATASOURCE
Here is an OPENROWSET example
INSERT INTO YourTable
SELECT * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls','SELECT * FROM [Sheet1$]')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Also refer
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
Madhivanan

Importing data

I have created a DTS package for SQL Server, saved this as a VB file and upgraded this to the .NET framework.

It is now saved in a file called Shortages.vb.

What I want to do now is add this to an existing ASP.NET project and be able to call this DTS function by the click of a button.

Is this possible and how can I go about it? Can I just add it to the Click event of a button?You could write a simple stored proceduure to call your DTS, and then execute your sproc from the web page button.|||I have considered that.

I tried to run it by calling it in the format below but

CREATE PROCEDURE doPublish ASexec master..xp_cmdshell 'dtsrun /Ssql1 /NPublish /E'

This is what I got when I tried to run the DTS package within SQL Sever Query Analyser.

If I put the Excel file on the desktop it works fine but it has a problem when its on the H: drive (a shared drive)

DTSRun: Executing...
DTSRun OnStart: Create Table [GE].[dbo].[tester] Step
DTSRun OnFinish: Create Table [GE].[dbo].[tester] Step
DTSRun OnStart: Copy Data from Results to [GE].[dbo].[tester] Step
DTSRun OnError: Copy Data from Results to [GE].[dbo].[tester] Step, Error = -2147467259 (80004005)
Error string: The Microsoft Jet database engine cannot open the file 'H:\Book1.xls'. It is already opened exclusively by another user, or you need permission to view its data.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003051

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file 'H:\Book1.xls'. It is already opened exclusively by another user, or you need permission to view its data.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003051

DTSRun OnFinish: Copy Data from Results to [GE].[dbo].[tester] Step
DTSRun: Package execution complete.
NULL

How can I make it run so that I can read from the H: drive?
It works if I run the package immediately when I create it with the DTS Import/Export Wizard.|||Does anyone have an answer tothis? I get the same error when I try to call my DTS package toaccess an excel file on my network.