Error when file is saved as a PDF from an SSRS report called within an SSIS script package

Question

I am trying to download an SSRS report in PDF format using an HTTP connection that is called in an SSIS script task. I get the error "could not open because it is either not a supported file type or because the file has been damaged.". The ultimate goal is to just have the SSRS report saved within the folder location that is stored in the variable "AEIOutputStagingFileFullPath" as a PDF file. Here is the actual code within the SSIS script task

   protected void SaveFile(string url, string localpath)
    {
        System.Net.HttpWebRequest loRequest;
        System.Net.HttpWebResponse loResponse;
        System.IO.Stream loResponseStream;
        System.IO.FileStream loFileStream = new System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
        byte[] laBytes = new byte[257];
        int liCount = 1;
        try
        {
            loRequest = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
            loRequest.Credentials = System.Net.CredentialCache.DefaultCredentials;
            loRequest.Timeout = 600000;
            loRequest.Method = "GET";
            loResponse = (System.Net.HttpWebResponse)loRequest.GetResponse();
            loResponseStream = loResponse.GetResponseStream();
            while (liCount > 0)
            {
                liCount = loResponseStream.Read(laBytes, 0, 256);
                loFileStream.Write(laBytes, 0, liCount);
            }
            loFileStream.Flush();
            loFileStream.Close();
        }
        catch (Exception ex)
        {
        }
    }

    public void Main()
    {
        ConnectionManager conn = Dts.Connections["AccountSTP_HTTPCon"];
        HttpClientConnection httpConn = new HttpClientConnection(conn.AcquireConnection(null));
        string outputSTPpdf = Dts.Variables["User::AEIOutputStagingFileFullPath"].Value.ToString();
        string pdfUrl = httpConn.ServerURL = @"http://vwdsrsdba0001/reports/report/TGAP/AccountChanges_STP&rs:Command=Render&rs:Format=PDF&rc:Toolbar=False";

        SaveFile(pdfUrl, outputSTPpdf);



        Dts.TaskResult = (int)ScriptResults.Success;
    }

In a client app I do it like this: https://stackoverflow.com/q/30095445/495455 - where did get the Code to do it with HttpClientConnection? Is that the recommended way as a SSIS script task? — Jeremy Thompson, Mar 24 '20 at 00:31
In this example they use a WebRequest - suggest you run through this method: https://www.mssqltips.com/sqlservertip/3475/execute-a-sql-server-reporting-services-report-from-integration-services-package/ — Jeremy Thompson, Mar 24 '20 at 00:35
@JeremyThompson I edited the code, and tried this... Still same result. I used the example web request that you told me about. — Jeffrey Padgett, Mar 24 '20 at 01:08

score 1 · Accepted Answer · answered Mar 24 '20 at 16:20

1

I use webClient to do this:

        using (WebClient wc = new WebClient())
        {
            bool retry = true;
            int retryCt = 0;

            wc.Credentials = new System.Net.NetworkCredential([insert user],[insert password]);

            while (retry)
            {
                try
                {
                    wc.DownloadFile(@"http://[insert server]/Reportserver?/[insert folder]/[insert report name]&rs:Command=Render&rs:Format=PDF&rc:Toolbar=False&[query string of params are optional]"
                                    , filepathName);
                    retry = false;
                }
                catch
                {
                    retryCt++;
                    if (retryCt >= 10) { retry = false; }
                }
            }
        }

answered Mar 24 '20 at 16:20

KeithL

5,348
3
19
25

This unfortionatly did not work. Same exact issue. I think it has something to do with the http request not going through before it can download async? – Jeffrey Padgett Mar 24 '20 at 19:25
Would it have something to do with this? https://stackoverflow.com/questions/10604774/datastream-length-and-position-threw-an-exception-of-type-system-notsupportede – Jeffrey Padgett Mar 24 '20 at 20:02
I literally use this exact code a lot. I use it in lieu on subscriptions so I can compile multiple reports into a single email. I haven't done it async though. – KeithL Mar 24 '20 at 22:44
I found the answer. I was using the wrong URL in the report string. It was “native” or some bull crap. It works now. This code works. – Jeffrey Padgett Mar 24 '20 at 22:45
1

Why don't you just try and put the URL into a browser and see if you download the file? You will be prompted for credentials. – KeithL Mar 24 '20 at 22:45

Error when file is saved as a PDF from an SSRS report called within an SSIS script package

1 Answers1