Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS XML file not found in 2012, but works in 2008?


SSIS XML file not found in 2012, but works in 2008?

Author
Message
dobbinjp
dobbinjp
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 46
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.
stevefromOZ
stevefromOZ
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1929 Visits: 3754
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.
dobbinjp
dobbinjp
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 46
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.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 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.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.
dobbinjp
dobbinjp
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 46
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!
dobbinjp
dobbinjp
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 46
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.
DB_Andrew
DB_Andrew
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 271
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!
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19502
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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 1095
Just what I needed. Thanks!
VoltronSabreRider
VoltronSabreRider
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 124
Many thanks for this solution I am having exactly the same problem. And everything started to work.

F
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search