0

I'm using a script task to download a CSV file from a Share Point library, I can use File.ReadAllLines() and the code will write the data from my data table to the System.Object variable just fine on my dev machine but fail when deployed to the server.

I've checked the access the account reading from the library has and that doesn't seem to be the issue, so I decided to try using a method that could leverage the default .net credential cache. I've tried a WebClient and a HttpWebRequest and they will download the CSV but cause a invocation error at the last step when I need to write the data from the data table in local memory to the package scoped Object variable.

Any thoughts on why the strange interaction between the download method is interfering with writing to the variable?

Here's the error: here's a screen shot of the error

Here's the code:

public void Main()
{
    //read the value of the index from the package variable
    int index = (int)Dts.Variables["User::SQLIndex"].Value;

    Array reports;

    string filePath = "https://servername/filepath/MobiusRepts.csv";
    HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(filePath);
    request.Method = "GET";
    request.Accept = "text/html";
    request.Credentials = System.Net.CredentialCache.DefaultCredentials;
    WebResponse GetResponse = request.GetResponse();
    //create a stream reader for the response 
    StreamReader reader = new StreamReader(GetResponse.GetResponseStream(), Encoding.UTF8);
    //response is the full response string converted to string
    string response = reader.ReadToEnd();
    //Dts.Events.FireInformation(0, "downLoadCSV", response.ToString(), String.Empty, 0, ref fireAgain);

    reports = response.Split(new String[] {"\r\n"}, StringSplitOptions.None);
    Dts.Events.FireInformation(0, "reportsLength", reports.Length.ToString(), String.Empty, 0, ref fireAgain);


    //declare a data table
    DataTable tempReportsTable = new DataTable();
    tempReportsTable.Columns.Add("ReportEnvironment");
    tempReportsTable.Columns.Add("ReportName");
    tempReportsTable.Columns.Add("ReportKey");
    tempReportsTable.Columns.Add("ReportID");
    //set type to int32 to pass it into sql more easily
    tempReportsTable.Columns.Add("SQLID", typeof(Int32));

    // int index = 0; //an index for the sql rows
    string[] row = { "", "", "" }; // a string array to hold the split lines
    //use a for each loop to iterate over the array
    foreach(string line in reports) {
    row = line.Split(';');
    // create a new data row
    DataRow dataRow = tempReportsTable.NewRow();
    // clean up the environment and name values
    dataRow["ReportEnvironment"] = row[2].Substring(0, 4);
    dataRow["ReportName"] = row[1].Trim();
    // create the report key using the id and env.
    dataRow["ReportKey"] = row[0].Trim() + ":" + row[2].Substring(0, 4);
    dataRow["ReportID"] = row[0].Trim();
    // set the index for the id in the sql table
    dataRow["SQLID"] = index;
    //add the row to the table
    tempReportsTable.Rows.Add(dataRow);
    Dts.Events.FireInformation(0, "Loop", index.ToString(), String.Empty, 0, ref fireAgain);
    Dts.Events.FireInformation(0, "loop test", row[1].Trim() + ", " + row[0].Trim() + ":" + row[2].Substring(0, 4), String.Empty, 0, ref fireAgain);
    //increment the index 
    index++;
    row.Initialize();
    }

    //push the data table into the object
    //Dts.Variables["User::ReportsObject"].Value = new DataTable();
    Dts.Events.FireInformation(0, "table test", "success", String.Empty, 0, ref fireAgain);
    Dts.Variables["User::ReportsObject"].Value = tempReportsTable.DataSet;

    Dts.TaskResult = (int)ScriptResults.Success;
}
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • please add the exact error message as text into your questions text (eidt, no screenshot, not as comment) . you might also want to remove some of the commented code to make it less big blob of code. thanks – Patrick Artner Jan 16 '18 at 18:15
  • What you posted after "Here is the error" is NOT the error. It is the call stack of everything leading up to the error, but it does not tell us what the error is. Without seeing your actual error, I don't think anybody will be able to help you. – Tab Alleman Jan 16 '18 at 20:54
  • @TabAlleman sorry about that, I didn't realize the error wasn't in the text I had copied. I've attached a screeshot of it, and here's the text itself: Exception has been thrown by the target of an invocation. – CSharpQuestions Jan 17 '18 at 15:03

0 Answers0