I have a package that exports data to a text file. The package runs perfectly from Visual Studio. No errors. But when I try to execute the package from the catalog in management studio I get the error "Exception has been thrown by the target of an invocation."
The first step is a script task that opens the file and appends a header to that file. This is where the error is occurring. I have a variable created with the header text and the following code is used to open the file and append the header:
string filePath = Dts.Connections["SOIL"].AcquireConnection(Dts.Transaction).ToString();
File.WriteAllText(filePath, string.Empty);
// Open the file from the connection manager to append some text
using (StreamWriter sw = File.AppendText(filePath))
{
// Append text from string variable to file
sw.WriteLine(Dts.Variables["User::Header1"].Value.ToString());
}
Dts.TaskResult = (int)ScriptResults.Success;