Sql-server – Error when running job using SSIS package

sql serversql-server-2008-r2ssis-2008

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

Best Answer

So the SSIS service has been installed, but is it actually running? Also, make sure that the domain and username are the same as those you used to setup the service. You need to check the permissions settings for the database and server you are trying to configure.

Related Solutions

Sql-server – Login failure when running a SSIS package from a SQL Server job

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.

Sql-server – Error while running SSIS Package job in SSMS

The package itself is not failing, it is failing to Load. Either your package or it's configuration must have changed.

Firstly, the error that is preventing your job from running is "The connection type "SPCRED" specified for connection manager "SharePoint Credential" is not recognized as a valid connection manager type." The SharePoint connector is not a native SSIS component. Were there any server changes that would require your third party SharePoint component to be re-installed, upgraded, re-licensed or otherwise re-configured?

If that is all OK then looking at the way you are running the job and the error statement "CPackage::LoadFromXML fails" suggests that your connection parameters are being sourced from a SSIS XML .config file. So you should check the config file to check that the connection strings are OK and have not been changed.

If none of that fixes it you will need to open the package in a development environment and debug it from there. Debugging SSIS packages is beyond the scope of this discussion without much more detail of the package in question.

Related Question