Downloading a file from http location, I get the following error

1
votes

Scenario

Downloading an xlsx file to my local drive from a http location using a execute script task .

ERROR

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized.

at System.Net.WebClient.DownloadFile(Uri address, String fileName)

at ST_3926c89e45574d05b1869761d015ffc0.csproj.ScriptMain.Main()

--- End of inner exception stack trace ---

at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Task failed: Script Task

Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package.dtsx" finished: Failure.

1
sql-server-2008ssis
Without the Script Task code, I can't be more specific, but the issue here is the credentials you're running your SSIS package under do not have authorization with the web service that's downloading the XLSX. How are you executing the package, and what credentials do you need for the web service? – Eric
Would you mind reviewing your open questions and either selecting answers or providing updates on why the proposed answers do not solve your problems? – billinkc
Cant paste in the code here. I can open this website on a normal internet explorer. I assume it the same user is SSIS . How can I check this ? I can't chnage the permissions of this file on the website, or can I ? Changed the security level of package and it still happens. Running the package in management studio gives the same error. – James Khan
If you are the user running the SSIS package (either in BIDS or from the command line) and you can download the file from the website using IE (which implies Windows Credentials) then you will need to supply the Credentials of the current user to the web service. Assuming you used the code from the previous question, it is attempting to access the site with Anonymous access. To correct it, use the code supplied in my answer – billinkc

1 Answers

2
votes

Based on the code provided in your previous (and open) question I presume you would need to set the Credentials property on the HttpWebRequest. Without knowing the specifics of your credential situation, I cannot say whether the DefaultCredentials will work or whether you will need to provide a user & password.

wr.Credentials = System.Net.CredentialCache.DefaultCredentials;