I have a SSIS package that will load data from a set of excel files then archive those files to a specified folder.
The excel files are stored in a folder and inside that folder I have the archive folder.
Below is my script task code for reference.
public void Main()
{
// TODO: Add your code here
string sourceDir = Dts.Variables["User::strFilePath"].Value.ToString();
string destDir = Dts.Variables["User::strArchivePath"].Value.ToString();
DirectoryInfo di = new DirectoryInfo(sourceDir);
string[] sDirFiles = Directory.GetFiles(sourceDir);
FileInfo[] fi = di.GetFiles("*.xls");
int fileCnt = fi.Length;
for (int i = 0; i < fileCnt; i++)
{
String filename = fi[i].Name;
string[] splitFilename = filename.Split('.');
DateTime dt = DateTime.Now;
string ArchiveDate = String.Format("{0:ddMMMyyyy}", dt);
string sourceFileName = filename;
string sourceFilePath = sourceDir + filename;
string destinationFileName = splitFilename[0] + '_' + ArchiveDate + '.' + splitFilename[1];
string destinationPath = destDir + destinationFileName;
//MessageBox.Show("Source File " + sourceFilePath + " to destination " + destinationPath);
if (File.Exists(destinationPath))
File.Delete(destinationPath);
// To move a file or folder to a new location:
System.IO.File.Move(sourceFilePath, destinationPath);
}
Dts.TaskResult = (int)ScriptResults.Success;
}
The sourceDir and destDir are variables that provides the path of source files folder and archive folder. The package works fine when I run it from visual studio.
I have deployed it to run as a job using deployment utility by creating manifest file. When I run the job I'm getting error in my archive script task. Below is the screenshot of it.
I searched for solution in stackoverflow however the provided solutions does not solve my problem.
Problem using SQL Agent to run SSIS Packages - fails with DTSER_FAILURE(1)
DTSX package runs in Visual Studio but not when called from a Database Job
I have granted read write access to SQL server for both the folders. Still getting the same error? Also have verified the excel runtime and it has been set to 32Bit.
Any help is highly appreciated.
Other recent topics
