This week, I resolved one issue which I received continuously which is nothing but exactly displayed in the post title. The issue occurred when I ran ssis package with batch file or scheduled job and same error message raised.
Source: Start Execute SQL Task
Description: Failed to acquire connection "<Connection Name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
As per title, it seems an issue with connection of server configured in ssis package. I thought same because this type of error comes when server is not connected or it does not have proper and rights to connect. First, I checked all server connections and tried to resolve it, but don't succeed.
Please note I received this error only while running ssis package with scheduled job or batch file, But it was working fine while running ssis package directly. It means it is not an issue in ssis package, but it is with batch files or schedule job. After trying I visited one conversion which says it may be issued with 32 bit/64 bit mode. And here I configured size package with schedule job and batch file with servers having operating system 64 bit.
After changing ssis package with schedule jobs and batch file with 32 bits, it works fine for me. Let me share,
With Batch file,
I changed path of dtexec.exe from <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn to <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn like following batch file content,
With Scheduled job,
I am sharing one screen shot of schedule job step configuration of ssis package,
The day after this changed, I always see this package succeed. I am leaving one discussion open here “why it was failing with a 64 bit mode run?
Source: Start Execute SQL Task
Description: Failed to acquire connection "<Connection Name>". Connection may not be configured correctly or you may not have the right permissions on this connection.
As per title, it seems an issue with connection of server configured in ssis package. I thought same because this type of error comes when server is not connected or it does not have proper and rights to connect. First, I checked all server connections and tried to resolve it, but don't succeed.
Please note I received this error only while running ssis package with scheduled job or batch file, But it was working fine while running ssis package directly. It means it is not an issue in ssis package, but it is with batch files or schedule job. After trying I visited one conversion which says it may be issued with 32 bit/64 bit mode. And here I configured size package with schedule job and batch file with servers having operating system 64 bit.
After changing ssis package with schedule jobs and batch file with 32 bits, it works fine for me. Let me share,
With Batch file,
I changed path of dtexec.exe from <drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn to <drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn like following batch file content,
"C:\Program Files (x86)\Microsoft SQL Server\100\dts\Binn\dtexec.exe" /FILE "C:\SSIS\SSISTest\bin\testing.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
With Scheduled job,
I am sharing one screen shot of schedule job step configuration of ssis package,
The day after this changed, I always see this package succeed. I am leaving one discussion open here “why it was failing with a 64 bit mode run?
No comments:
Post a Comment
Please Use Good Leanguage