Execute Script task throws error "The package execution returned DTSER_FAILURE (1)"

Question

Post reply

Execute Script task throws error "The package execution returned DTSER_FAILURE (1)"

karthik82.vk

SSCrazy

Points: 2002
More actions
September 6, 2015 at 8:05 am

#302893

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.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?
Any help is highly appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 1

Hi

I'm afraid your screenshot is unavailable for us, please tell me source and dest dir are local or network paths?

Best regards

Mike

karthik82.vk SSCrazy Points: 2002 More actions · Answer 2

Hi,

Both the source and dest dir are local path and is located in the same system the package is running.

The SourceDir is D:\SourceFiles\Pro001

and the destDir is D:\SourceFiles\Pro001\Archive

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 3

Hi

Please set Run64BitRuntime to false in your Package and check again that

the account running the SQL Server Agent have permissions to the files and folders involved.

Best regards

Mike

karthik82.vk SSCrazy Points: 2002 More actions · Answer 4

Hi,

I have the runtime 64 enabled as false.

I am using a proxy account for running the sql package.

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 5

Hi

Please add folder C:\Windows\System32\config\systemprofile\Desktop

(to run 64-bit mode C:\Windows\SysWOW64\config\systemprofile\Desktop).

Best regards

Mike

karthik82.vk SSCrazy Points: 2002 More actions · Answer 6

Hi Michal,

Can you tell me why i need to add the folder to run 64bit mode?

How this is connected with my problem... Just to know the reason...

Also tell me how i can do this as I am unaware of the process..

karthik82.vk SSCrazy Points: 2002 More actions · Answer 7

Hi Michal,

Tried your solution of creating Desktop in SysWow64...

However i'm getting the same error

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 8

Hi

Sorry for lack of explanation from my side, regarding your questions:

Can you tell me why i need to add the folder to run 64bit mode?

I put it as an option and if you run in 32-bit mode only you don't have to create that folder.

How this is connected with my problem... Just to know the reason

I'm not fully familiar with your project but I your error guided me on problem that I met in the past,

please take a look for this link: http://www.c9q.net/q/28759081

Please review your code again, imho it should look like that:

public void Main()

{

//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

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++)

foreach (var fi in di.GetFiles("*.xls"))

{

String filename = fi.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;

}

Best regards

Mike

Dung Dinh SSCrazy Points: 2513 More actions · Answer 9

Hi,

When you run the package in Visual Studio, it uses your account to run, which you logged in. However, you run in SQL Agent Job, it uses the account of SQL Agent Service to run. Open services and check which account is configured SQL Agent Service and make sure that it has permission on source / destination folder.

Thanks,

karthik82.vk SSCrazy Points: 2002 More actions · Answer 10

Hi,

Thanks for your reply. I will check and update if the problem is resolved.

karthik82.vk SSCrazy Points: 2002 More actions · Answer 11

Hi All,

I replaced the script task with a file system task and it worked.