none
SSIS XML Data Source - worked in BIDS, can't read XML in SSDT

    Question

  • I have an SSIS 2008 package built in BIDS (2008) that uses a series of XML locations (similar to http://corpslocks.usace.army.mil/lpwb/xml.tonnage?in_river=AG&in_lock=42&in_mon_yr=092013) to get tonnage information and load it into a SQL Server 2008 R2 database. This has been running daily without error for over 60 days. I am now migrating this to SSIS 2012 using SQL Server Data Tools and I am having difficulty reading the XML into the database.

    The package does the following (it is pretty simple):

    1. read the URLs (there are 200) from a database view into a variable object
    2. a for each loop container that loops through the variable object, gets the data from each URL (the XML URL is stored as a variable) and loads it into the database

    The BIDS package still works great. I first used the converter to migrate to 2012. I then recreated a package with exactly the same settings as BIDS. No dice either way. I also tried using a local xsd file, then using one inline. SSDT will recognize all of the columns correctly when I use inline, but neither method is capable of reading the data into a table.

    I know the package is going to the addresses, all of the URLs show up in the error logs. The error messages are as follows: "[Load data from tonnage url [47]] Error: The file "http://corpslocks.usace.army.mil/lpwb/xml.tonnage?in_river=AG&in_lock=42&in_mon_yr=092013" was not found. Please verify the file path and try again."
     
    Has anyone run into this and solved it? I think I have tried every possible setting available, with no luck. Any ideas or suggestions are appreciated. Thanks!

    Thursday, September 26, 2013 9:57 PM

Answers

All replies

  • Friday, September 27, 2013 5:50 AM
    Moderator
  • I have an update on this issue. I believe it is a connectivity issue or possibly a setting in SSDT. 

    I can access the remote XML file location through my browser. I have downloaded the XML from the remote location and my package runs using the local XML file perfectly. I have BIDS 2008 installed on the same machine and it works without a problem (using the remote XML location). This last piece makes me think there is a network setting or something in SSDT.

    Does anyone have any additional troubleshooting advice or other suggestions? My error output is included below. 

    Thanks!

    *************************

    Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning.
    Error: 0xC020F440 at Data Flow Task, XML Source [31]: The file "http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01" was not found. Please verify the file path and try again.
    Error: 0xC02090D0 at Data Flow Task, XML Source [31]: The XML Source was unable to read the XML data.
    Error: 0xC0047019 at Data Flow Task, SSIS.Pipeline: XML Source failed the prepare phase and returned error code 0xC02090D0.
    Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "ADO NET Destination" wrote 0 rows.
    Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.
    Task failed: Data Flow Task

    ***************************

    Monday, September 30, 2013 3:21 PM
  • Hi dobbinjp,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Thanks,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, October 01, 2013 9:25 AM
    Moderator
  • I have another update to post. I did a wireshark trace of network action during a successful run of the package in BIDS 2008 and of an unsuccessful run in SSDT 2012. These packages have the same settings (hitting the same urls, using the same database, etc.).

    The successful run has GET requests similar to the following using the HTTP protocol:

    208.87.234.180 HTTP 681 GET http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=AG&in_lock=45 HTTP/1.1 

    The unsuccessful run has no HTTP GET requests...it looks to me like there are TCP requests generated in the logfile when it tries to go to "http://corpslocks..." urls, although addresses similar to the one shown in the above sample log entry are nowhere to be found.

    10.1.13.103 TCP 270 30585 > 62964 [PSH, ACK] Seq=7690 Ack=25761 Win=65024 Len=216 

    Is there some kind of intranet setting in SSDT that might be keeping it from going out to the Internet? Any other ideas? Thanks again for any help or suggestions.

    Tuesday, October 01, 2013 2:58 PM
  • Just another follow up...when I am designing the package and setting up the XML data source, SSDT can retrieve the column definitions from the URLs. Then, when it is time to run the package, it cannot connect to the same URL it just generated in-line XSD from...that is what is leading me to think it is a setting or something in SSDT.
    Tuesday, October 01, 2013 3:05 PM
  • Okay, I think we are getting somewhere. It looks like the reason it works in BIDS is that was already installed on my computer and it uses our web-filtering software proxy. I installed SSDT after that and did not configure it to use the proxy. I will post up later to confirm this, but that is my thought on it now. 

    Anyone have pointers on how to configure proxy auto configuration (PAC) files in SSDT and SSIS?

    Tuesday, October 01, 2013 4:05 PM
  • Okay, I finally got around to testing at home and got the same exact result, and it is NOT the web filtering software. I created a remote XML file using the template here (http://msdn.microsoft.com/en-us/library/ms762271(v=vs.85).aspx) and it does not work, either, so it is not the format or something special on the Corpslocks URL. 

    Does anyone know how to use web XML data in SSIS 2012? This works so easily in 2008 (http:// path in the XML source) and it seems so agonizingly simple, but I cannot connect for the life of me in 2012. Once the file is downloaded I can import the data without a problem, but I need to get my updated data from the web. 

    Thanks in advance for any help/guidance.
    Friday, October 04, 2013 4:23 PM
  • Not sure if you have already tried it or not....instead of migrating via SSDT, can you try creating a brand new package in SSIS2012 consuming the web xml? Lemme know how it goes. Also we have fixed several issues in the latest SSDT release (Oct 2013)...so make sure you are running the latest version: http://msdn.microsoft.com/en-us/data/hh297027
    Tuesday, October 22, 2013 10:06 PM
  • Marking this thread as closed.
    Wednesday, October 30, 2013 11:48 PM
  • did you ever get an answer on this? we just moved to 2012 ssdt and I am having exact same problem: worked in 2008, doesn't in 2012 ssdt.  I can paste the url in a browser and it returns correct data...

    thanks for any and all help

    mike


    Mike Dugan

    Wednesday, August 06, 2014 5:56 PM
  • This was a tough one, but I finally figured it out. It did not seem possible to do remote XML download/retrieval in SSDT as it is possible in BIDS. The workaround is to create a script that loads the XML into a variable then you can treat that variable like a locally downloaded XML file. I was able to get this working and it has been running great for 10 months now. 

    Here is a web resource I used that guided me through it:

    http://www.sqlwithcindy.com/2013/03/suck-it-up-consuming-api-that-returns.html

    Hope that helps!

    Wednesday, August 06, 2014 6:12 PM