I have SQL Server 2008R2 installed on my Windows server 2008. I created a package using the Import and Export Wizard from file system. After that I created a job (SQL Agent Job ) that used the SSIS package I created in the first step.
After I run this job I would receive the following error:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 10/10/2017 17:38:12,update job,Error,0,DBServer\ABC,update job,(Job outcome),,The job failed. The Job was invoked by User mydomain\myuser. The last step to run was step 1 (update step 1).,00:00:10,0,0,,,,0 10/10/2017 17:38:12,update job,Error,1,DBServer\ABC,update job,update job,,Executed as user: mydomain\myuser$. Microsoft (R) SQL Server Execute Package Utility Version 10.0.6000.29 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 5:38:12 PM Error: 2017-10-10 17:38:17.80 Code: 0xC00291EC Source: Drop table(s) SQL Task 1 Execute SQL Task Description: Failed to acquire connection "DestinationConnectionAdoNET". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2017-10-10 17:38:17.81 Code: 0xC00291EC Source: Preparation SQL Task 1 Execute SQL Task
Description: Failed to acquire connection "DestinationConnectionAdoNET". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 5:38:12 PM Finished: 5:38:17 PM Elapsed: 5.578 seconds. The package execution failed. The step failed.,00:00:10,0,0,,,,0
I did some research and I found that the SSIS service was not installed. I installed SSIS and I can connect to it using SQL Server Management Studio. After that I created new job using same file but still I received same error as before. Please advise how to fix this problem
