I creating an SSIS package which has a script task in it.
In the script task, i have provided the details of connection string and the sql statements to get executed.
The problem is , when i try to execute the package, i'm getting the following error:
"Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection."
My connection string is like:
"Data Source=SQLServerName;Integrated Security=false;Initial Catalog=STG_DB;Provider=SQLNCLI10.1;Persist Security Info=True;Userid=uid;Password=pwd;Auto Translate=False;"
However, when I change the "Integrated Security=false" to "Integrated Security=SSPI", the package gets executed successfully..
I also verified that the following checkboxes are checked in the Component Services -> My Computer -> DTC -> Local DTC Properties.
"Network DTC Access"
"Allow Remote Client"
"Allow Remote Administration"
I'm using Windows 7.
Please let me know your suggestions in fixing this issue...