September 30, 2013 at 12:25 pm
Hello,
I have an SSIS 2008 package that goes to the following url (http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01) and downloads the data into a SQL Server 2008 R2 table. I am trying to update this package to SQL Server 2012 and am having what I think are connectivity or settings issues in SSDT.
Here is what is happening:
1. As I mentioned, the package (XML data source and SQL Server ADO.NET destination) works great in BIDS 2008.
2. I have used the upgrade wizard as well as created a new package from scratch in SSDT 2012 and verified all of the settings are the same as in the 2008 working version. I get the same results with each (upgrade wizard and from scratch).
3. I can access the remote url from my browser, and when I specify this URL (http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01) as a file location for an XML source in design mode, it reads the columns and data types when generating the xsd (it appears to make the connection).
3. When I run the package, it fails with "http://corpslocks.usace.army.mil/lpwb/xml.lockqueue?in_river=GI&in_lock=01 cannot be found, please verify the path and try again..." and "XML source was unable to read the XML data."
4. There is no problem with the XML since I downloaded the file and the package works as expected with a local XML path.
Any thoughts or suggestions on what might be causing this? Thanks in advance for any help.
September 30, 2013 at 5:27 pm
It's not pretty, but you could always try something like this script to download the ml to a local drive then read it per the XMl task.
Steve.
October 1, 2013 at 9:04 am
Thanks for the suggestion, but I am trying to get this running completely within SQL Server without the need for downloads, etc. This behavior is really weird, and I think it might be a network connectivity setting...very strange that it will generate inline XSD from an internet location, but when it comes time to run and hit the exact same url, it can't find the same location it was just at to get the XSD.
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.180HTTP681GET 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 in the log...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. I see in the SSDT output log where it tries to get XML from (what I verify to be) valid URLs. This is a sample TCP entry where I assume SSDT is trying to get to the location of the XML:
10.1.13.103TCP27030585 > 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.
October 1, 2013 at 12:05 pm
Another update...I now think this is due to my company's web filtering. I installed SSDT after the desktop image was created (which included BIDS, which is why that worked). I am now trying to learn how to get SSDT and SSIS to use a proxy (proxy auto-config file: PAC)...any suggestions on that?
Thanks!
October 4, 2013 at 10:18 am
Okay, another update...this issue is not being caused by web filtering. I have tried it from home and another location without web filtering and I get the same result. I have tried a basic xml file and I get the same result, so it is not the formatting of 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 simple, but I cannot connect for the life of me in 2012.
Thanks in advance for any help/guidance.
September 19, 2014 at 1:39 pm
Here is the fix for VS 2010 and VS 2012.
FIX: SSIS 2012 XML Source task cannot load data from a URI
http://support.microsoft.com/kb/2991526
Enjoy!
September 20, 2014 at 2:50 am
DB_Andrew (9/19/2014)
Here is the fix for VS 2010 and VS 2012.FIX: SSIS 2012 XML Source task cannot load data from a URI
http://support.microsoft.com/kb/2991526
Enjoy!
And for those who would rather just click on URLs:
http://support.microsoft.com/kb/2991526
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 17, 2014 at 1:10 pm
Just what I needed. Thanks!
December 17, 2014 at 9:06 am
Many thanks for this solution I am having exactly the same problem. And everything started to work.
F
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply