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 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;
}