August 25, 2004 at 9:41 am
I'm trying my first steps with DTS.
I have an XML file that contains info I want to append to a table in an SQL db. This will be done every day. I assumed I could 'tell' the machine to connect to the file containing the XML file, copy it to an internal table before appending it to the destination table in my SQL db.
Is this possible without wrting code (or with minimum amount of code)? I've tried bulk insert that connects to the XML file. This is as far as I went since I'm unable to define the right format for the insert in the db table I'm trying to import data in.
Any help appreciated.
Jeam
August 26, 2004 at 3:01 am
I've been struggling with DTS since I started using it 2 years ago (but when you make it work it's brilliant), but using Google "import XML" SQLXML Microsoft ... (210 links) you should find something like:
316005 - How to import XML into SQL Server with the XML Bulk Load component
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q316005
Good luck
Alain
Alain
August 30, 2004 at 8:36 am
I tried the suggestion of the MS article 316005 and I can not go past the described error "Error connecting to the data source" even though I copied the code as is on the MS page.
Any idea why is that happening? I even created a user "test" with password "test". I also tried referencing the data source by giving the computername (in my case: data cource=MyComputer\MySQLServer)
Please help!
Jean-Marie
August 30, 2004 at 8:42 am
Jean-Marie,
I have done a little with the XML import so I don't know how much I can help - could you post some of the code so I can look at it?
August 30, 2004 at 9:10 am
I've litterally copied the example code from the MS web site. Here it is (with my parameters)
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;datasource=JMRSQLBI;database=MyDatabase;uid=test;pwd=test"
objBL.ErrorLogFile = "c:\xml\MyError.log"
objBL.Execute "c:\xml\MyCustomermapping.xml", "c:\xml\MyCustomers.xml"
Set objBL = Nothing
MyCustomermapping.xml stores the schema (let me know if you need a look at it too)
But I think that the error is occuring with the ConnectionString in the vbs script...
Thanks a million in advance
Jean-Marie
August 30, 2004 at 9:22 am
Try this:
'*************
'** Changed **
'*************
'Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
'**************
'** NEW Line **
'**************
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
'*************
'** Changed **
'*************
'objBL.ConnectionString = "provider=SQLOLEDB.1;datasource=JMRSQLBI;database=MyDatabase;uid=test;pwd=test"
'**************
'** NEW Line **
'**************
objBL.ConnectionString = "provider=SQLOLEDB.1;datasource=JMRSQLBI;database=MyDatabase;integrated security=SSPI"
'***************
'** NEW Lines **
'***************
objBL.KeepIdentity = False
objBL.CheckConstraints =True
objBL.SchemaGen = True
objBL.SGDropTables = True '** You might not want this line
objBL.XMLFragment = True
'***************
objBL.ErrorLogFile = "c:\xml\MyError.log"
objBL.Execute "c:\xml\MyCustomermapping.xml", "c:\xml\MyCustomers.xml"
Set objBL = Nothing
Hope it helps
August 30, 2004 at 9:47 am
Thanks for the hint but...
The SQLBulkLoad.3.0 does not work on my machine, so I replaced the statement by BulkLoad.2.0
The error remains. It's referencing the line of the objBL.Execute even if it's saying "Error connecting to the data source"
What's the correct statement on the data source parameter? Should it specify the ComputerName (in my case it would be data source=GO4\JMRSQLBI)
Thanks again.
Jean-Marie
August 30, 2004 at 9:56 am
If you are running it on your local SQL server then use this (local) for the datasource. If you have the option I would install SQLXML 3.0 on your machine (you can get it from Microsoft).
August 31, 2004 at 6:42 am
I've installed SQLXML 3.0 but still no success. Maybe someone has done this before? I'm trying get some grip on this process so I can use it in a Business Intelligence process. There will be some daily publication of sales data in XML that I plan to import in a SQL server db then use Analysis Services to build a cube for sales data analysis and reporting.
Maybe I just need ask the integration consultant to supply me some spreadsheet?
Thanks for your help.
Jean-Marie
September 1, 2004 at 6:47 am
It works now - don't know why!
Still, it's only copying the data and replacing the info in the table. Anything I need to do to "append" the data in the table?
All suggestions welcome.
Jean-Marie
September 1, 2004 at 7:23 am
Try changing this line from True to False
objBL.SGDropTables = True (False)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply