Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS XML file not found in 2012, but works in 2008? Expand / Collapse
Author
Message
Posted Monday, September 30, 2013 12:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:55 PM
Points: 4, Visits: 44
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.
Post #1500155
Posted Monday, September 30, 2013 5:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, December 17, 2014 6:34 PM
Points: 1,846, Visits: 3,575
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.
Post #1500225
Posted Tuesday, October 1, 2013 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:55 PM
Points: 4, Visits: 44
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.
Post #1500464
Posted Tuesday, October 1, 2013 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:55 PM
Points: 4, Visits: 44
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!
Post #1500535
Posted Friday, October 4, 2013 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:55 PM
Points: 4, Visits: 44
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.
Post #1501668
Posted Friday, September 19, 2014 1:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:25 PM
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!
Post #1615453
Posted Saturday, September 20, 2014 2:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1615504
Posted Monday, November 17, 2014 1:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:02 PM
Points: 130, Visits: 946
Just what I needed. Thanks!
Post #1635714
Posted Wednesday, December 17, 2014 9:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:31 AM
Points: 4, Visits: 102
Many thanks for this solution I am having exactly the same problem. And everything started to work.

F
Post #1644763
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse