SIS Package Execution, Windows Authentication, Error: Failed to acquire connection

I have developed one SSIS Package in SSDT 2012 to create Reporting Database from SQL Datasource and 1 SharePoint List.

On Development Environment : (Windows Authentication)

Here everything is running fine.

On Test Environment Client Side : (Windows Authentication)

Deployed successfully.

Validating successfully.

While executing the package: getting below error.

SSISPackage:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code : 0x80040E21. An OLE DB record is abailable. Source: "Microsoft OLE DB Service Componenets" Hresult: 0x80040E21 Description: "Multipe-step OLD DB operation generated errors. Check each OLD DB status value, if available. No work was done.".

Execute SQL Task: Error: Failed to acquire connection "ReportingDB". Connection may not be configured correctly or you may not have the right permissions on this connection.

I have done below things:

On SSIS Package side,

1. DelayValidation is Set to True for all the connections and each tasks in package

2. RetainSameConnection : True

3. ProtectionLevel : DoNotSaveSensitiveData

On SSMS,

1. Created one Credential

2. Create Proxy under SSIS SQL Service Agent to run package as Windows User

3. Checked all the permissions and roles.

Below is the connection string which I am using.

Data Source=DBTEST;Initial Catalog=ReportingDB;Integrated Security=SSPI; persist security info=False; Trusted_Connection=Yes;

Can anyone please help me? As I am trying to resolve this error for the last 4 days and could not find any solution yet.

Thank you,

Mittal.

Hopefully you have solved this by now, but in case anybody else has the same problem I had failed to specify the provider in the connection string. Here is an example connection string that worked for me for an OLEDB data source:

Data Source=localhost;Initial Catalog=dbname;Provider=SQLNCLI11.1;Integrated Security=SSPI;Connect Timeout=30;Auto Translate=False;

Also if you happen to be passing connection string details as parameters make sure they are properly mapped, but likely not the case here as you are using SQL Server agent.