DTS???

  • 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

  • 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


    Best Regards,

    Alain

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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).

  • 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

  • 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

  • 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