I have created a multi-step SSIS package in SQL Server 2008 R2 that runs fine when launched under DTS Packages in the Integration Service, but fails when called in a SQL Server Agent job on a step that involves copying to a SharePoint folder an Excel file created on the server. Within the package the Run64bitRuntime is set to False and when the package is called in a step of the job, the "Use 32 bit runtime" is checked under the Execution options. This works for other SSIS packages that are reading information from Excel files located in other SharePoint folders. What is unique here is that I am attempting to now copy to a SharePoint folder an Excel file created on the server. If it were not for the unqiue formatting and contents of the Excel file, I would be using SSRS instead.
Here is the error message returned when I select "View History" of the SQL Server Agent job.
Date 1/29/2013 3:13:47 PM Log Job History (Data Pull)
Step ID 1 Server xxxx-xxx-xx Job Name Data Pull Step Name Run xxxx_Extractxxx Duration 00:16:13 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message
Executed as user: CABLE!svcnvdeautomation. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved.
Started: 3:13:47 PM Error: 2013-01-29 15:30:00.01 Code: 0x00000001 Source: Create Excel file Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC at Microsoft.Office.Interop.Excel.WorkbookClass.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local) at ST_87e8d62a054b4e16b60297154afc19d8.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.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature 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 System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:13:47 PM Finished: 3:30:00 PM Elapsed: 972.573 seconds. The package execution failed. The step failed.
If it helps, listed below is the code being applied to copy the Excel file.
public void Main()
{
SharePointListsService.Lists listService = new SharePointListsService.Lists();
listService.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Create variable with format of "2012-11-28"
string DateTimeNow = DateTime.Now.ToString("yyyy") + "-" + DateTime.Now.ToString("MM") + "-" + DateTime.Now.ToString("dd");
// Check out the file as defined in SharePoint.
string fileCheckout = "http://xx.yy.com/Shared%20Documents/Task%20Compliance%20Report.xls";
bool myResults = listService.CheckOutFile(fileCheckout, "true", DateTime.Now.ToString("f"));
// Full file name including path and extension
// "D:\Work area\Reports\Project.xls"
string FullFileName = Dts.Variables["User::ReportsExcelFileName"].Value.ToString();
// File Name without path and extension
// "Project"
string FileName = GetFileNameWithoutExtFromPath(FullFileName);
// Path only
// "D:\Work area\Reports\"
string Path = GetDirFromPath(FullFileName);
// From the source file (FullFileName) assemble the [Path] and [Filename] of new file copy that includes a specific date format attached to new file name.
// "D:\Work area\Reports\Project 2012-11-28.xls"
File.Copy(FullFileName, (Path + "\\" + FileName + " " + DateTimeNow + ".xls"), true);
// Get UNC Folder path
// "\\xx.yy.com\Shared Documents\Data Pulls" string UNCFolder = Dts.Variables["UNCFolder"].Value.ToString();
// Get File name as it appears in the Reports folder
string ReportsExcelFile = FullFileName;
// Copy the Excel file from the Reports directory to the SharePoint UNC folder location
File.Copy(ReportsExcelFile, (UNCFolder + "\\" + FileName + ".xls"), true);
//Check in
//string fileCheckin = Dts.Variables["User::URLFolder"].Value.ToString();
string fileCheckin = "http://xx.yy.com/Shared%20Documents/Task%20Compliance%20Report.xls";
myResults = listService.CheckInFile(fileCheckin, "Completed revision.", "0");
Dts.TaskResult = (int)ScriptResults.Success;
}
public String GetFileNameWithoutExtFromPath(String path)
{
try
{
String filename = path.Substring(path.LastIndexOf("\\") + 1);
int pos = filename.LastIndexOf(".");
if (pos != -1)
return filename.Substring(0, pos);
else
return filename;
}
catch (Exception)
{
// error
return "";
}
}
public String GetDirFromPath(String path)
{
try
{
return path.Substring(0, path.LastIndexOf("\\") + 1);
}
catch (Exception)
{
// error
return "";
}
}
Under SQL Server Configuration Manager I have set the SQL Server Agent to apply a log on with network access, which is being applied successfully by other SSIS packages being called by other jobs. Also, under Componet Services on the server, I have gone to My Computer, right-clicked and selected properties, which opened the My Computer Properties box with six tabs. I selected the COM Security tab and edited Edit Limits for both Access Permissions and Launch and Activation Permissions. Under each I added NETWORK SERVICE. For Access Permissions I checked allow Local Access and for Launch and Activation Permissions, I selected Local Launch and Local Activation.
Given the above details, can somebody help me to determine why the SSIS package continues to fail for the step of copying over an Excel file when run under the SQL Server Agent?
I assume that the SQL job is running the SSIS package under SQL Server Agent Service Account, which may not have the right authority to copy/move files from one folder location to other. You need to set up a proxy account that uses a domain account (preferably) credentials. The domain account should have the right level of permissions to copy/move files between folders.
Answer in the below SO question walks step-by-step on how to configure a proxy account. It also explains how to use the proxy account to execute the SSIS package within the SQL job.
How do I create a step in my SQL Server Agent Job which will run my SSIS package?
At least, this is what I did when I had similar issues with moving files between folders.
Hope that guides you to find solution to your problem.