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

Question

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?

Any help is highly appreciated.

Answer 1

Try choosing 64bit on your Project --> Properties --> Dubugging--> Run64BitTime this could solve the problem.

Answer 2

I replaced the script task with a file system task and it worked. I also created a proxy account to run the package with my system credentials so that it will have the access to the folder specified.

Putting the steps in creating a proxy account for running the SQL job so that it will be helpful for users looking out for it..

Create a User account in SQL under which you need to run the Job Create a credentials for the user account created.

    --Script #1 - Creating a credential to be used by proxy
USE MASTER
GO 
--Drop the credential if it is already existing 
IF EXISTS (SELECT 1 FROM sys.credentials WHERE name = N'SSISProxyCredentials') 
BEGIN 
DROP CREDENTIAL [SSISProxyCredentials] 
END 
GO 
CREATE CREDENTIAL [SSISProxyCredentials] 
WITH IDENTITY = N'<Insert the Username>', 
SECRET = N'abcd@0987' 
GO

Create Proxy account and associate the credentials created

--Script #2 - Creating a proxy account 
USE msdb
GO 
--Drop the proxy if it is already existing 
IF EXISTS (SELECT 1 FROM msdb.dbo.sysproxies WHERE name = N'SSISProxyDemo') 
BEGIN 
EXEC dbo.sp_delete_proxy 
@proxy_name = N'SSISProxyDemo' 
END 
GO 
--Create a proxy and use the same credential as created above 
EXEC msdb.dbo.sp_add_proxy 
@proxy_name = N'SSISProxyDemo', 
@credential_name=N'SSISProxyCredentials', 
@enabled=1 
GO 
--To enable or disable you can use this command 
EXEC msdb.dbo.sp_update_proxy 
@proxy_name = N'SSISProxyDemo', 
@enabled = 1 --@enabled = 0 
GO

Granting proxy account to SQL Server Agent Sub-systems

USE msdb
GO 
--You can view all the sub systems of SQL Server Agent with this command
--You can notice for SSIS Subsystem id is 11 
EXEC sp_enum_sqlagent_subsystems 
GO

--Grant created proxy to SQL Agent subsystem 
--You can grant created proxy to as many as available subsystems 
EXEC msdb.dbo.sp_grant_proxy_to_subsystem 
@proxy_name=N'SSISProxyDemo', 
@subsystem_id=11 --subsystem 11 is for SSIS as you can see in the above image 
GO 
--View all the proxies granted to all the subsystems 
EXEC dbo.sp_enum_proxy_for_subsystem

Granting proxy access to security principals

USE msdb
GO 
--Grant proxy account access to security principals that could be
--either login name or fixed server role or msdb role
--Please note, Members of sysadmin server role are allowed to use any proxy 
EXEC msdb.dbo.sp_grant_login_to_proxy 
@proxy_name=N'SSISProxyDemo' 
,@login_name=N'<Insert the Username>' 
--,@fixed_server_role=N'' 
--,@msdb_role=N'' 
GO 
--View logins provided access to proxies 
EXEC dbo.sp_enum_login_for_proxy 
GO

Finally associate the proxy account to the package step. This can be also done through job wizard.

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SSISPackageCall', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'SSIS', 
@command=N'/FILE "C:\Package.dtsx" /CHECKPOINTING OFF /REPORTING E', 
@database_name=N'master', 
@flags=0, 
@proxy_name = N'SSISProxyDemo';

Thanks for the valuable replies for my post..