Schedule an SSIS package

[chvnpradeep2492]

Hi team,

I created multiple packages in SSIS. Source connection manager is Excel/ Oracle Database and Target connection manager is ODBC(Snowflake cloud) so we created packages and containers. Now, we want to schedule those.

I tried by deploying a project in SSMS and by creating a new job. In the job, I added job steps and gave a schedule time. Every time it is getting failed due to the below error. If I use target connection manager OLE DB(SQL) I'm able to schedule the packages successfully.

Date 3/22/2022 9:49:16 AM
Log Job History (JOB)

Step ID
Server DESKTOP-2B7TI5T
Job Name JOB
Step Name
Duration 00:00:03
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message :
The job failed. The Job was invoked by User DESKTOP-2B7TI5T\Rajeswari. The last step to run was step 1 (AR).

Below are the SQL server management studio Details:

SQL Server Management Studio 15.0.18358.0
SQL Server Management Objects (SMO) 16.100.44091.28
Microsoft Analysis Services Client Tools 15.0.19260.0
Microsoft Data Access Components (MDAC) 10.0.22000.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.22000

Please guide me how to achieve this with brief steps.

Thanks.

[#2]

@chvnpradeep2492 , the error message is the main one from the job; not very usefull.

Lookup the error message details from the job step instead.

Often cause of such error is a bitness confict between Agent job and data provider, e.g. a 64 bit job step using a 32 bit data provider. Is that the case here?

Olaf

[#3]

@olafhelper 

This is the error message for Job Step. My ODBC destination driver (snowflake) is also 64 bit . How to fix this can you provide the solution?

Log Job History (Snowflake_Job)

Step ID: 1
Server : DESKTOP-2B7TI5T
Job Name :Snowflake_Job
Step Name : AR
Duration 00:00:30
Sql Severity 0
Sql Message ID 0

Message
Executed as user: DESKTOP-2B7TI5T\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 15.0.2000.5 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. Started: 9:27:51 PM Package execution on IS Server failed. Execution ID: 65, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 9:27:51 PM Finished: 9:28:21 PM Elapsed: 29.609 seconds. The package execution failed. The step failed.

Thanks

[#4]

---

To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report

---

@chvnpradeep2492 , so the package is stored in SSISDB? Then do what the message says and review the package execution report.

Troubleshooting Reports for Package Execution - SQL Server Integration Services (SSIS) | Microsoft D...

Olaf

[#5]

@olafhelper 

we went through the error message and we are getting like below image.

Can you suggest where we fix this problem?

[#6]

@chvnpradeep2492 I still can only guess and if it works in SSDT/Visual Studio as 32 bit tool, but not as Agent job, then it's may a 32/64 bit issue.

Try to run the job as 32 bit, see

SQL Server Agent Jobs for Packages - SQL Server Integration Services (SSIS) | Microsoft Docs

=> 32-bit runtime

Olaf

[#7]

Hi Olaf,

As you told I validate the packages and execute the packages then those are running without errors. After creating a job & schedule a job we getting that issue so we want to schedule those job steps how can we do that? Can you provide a solution for this?

Thanks