Failed to acquire connection. Connection may not be configured correctly or you may not have the right permissions on this connection - SQL Server SSIS by serverku

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,

"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?

Apply discrepancies at destination using SSIS - tablediff Utility in SQL Server by serverku

The last time we saw tablediff Utility basis and tablediff utility for multiple tables using SSIS. As you know using sssis package it generated discrepancies log files for database changes which we have to apply on destination servers\databases to make them seem. Now this post is extended for the same in which we will take care to automatically apply the discrepancies for each file generated at destination.
I am writing here for the next portion of previous post using ssis package for multiple tables and changes to be generated. Let create remaining part and add to existing package.

In the previous post we have visited first three steps and here I added last three steps (4 to 6). For the steps (1 to 3) please visit this post. Let’s continue with the remaining steps,

Step 4 : For each loop container
Retrieving each log files for the process,

Here we have taken one more variable to capture full log file path in variable named “Filename” and the following process will do the same,

Step 5 : Execute SQL Task
Taking destination database connection and using file connection,

To apply multiple file changes to destination, taken expression for FileConnection,

Step 6 : File System Task
Moving files to other location after process, so does not repeat next time,

After completing and running all the steps we will have the destination database to same as source , let us run first three steps (1 to 3) after changes apply and verify if found any other discrepancies,

You can see after this run, we have a message by tablediff utility is “Source table and destination table are identical” and it won't generate any discrepancies log files more. I want to know you are using, these steps to apply changes to destination using ssis? Waiting for your comments!

SQL Server tablediff utility for multiple tables using SSIS by serverku

As we saw the last post for the basic concept of tablediff utility. We learned one example, using static table, now I would like to go it with SISS package and also using more tables comparison. So, lets start it with some demo objects and created as follows. Here I am creating two tables with different databases and same SQL server instance.

USE SourceDB
GO

CREATE TABLE dbo.SourceObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.SourceObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.SourceObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 2,'test2'
GO

INSERT dbo.SourceObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 2,'test4'
GO


USE DestDB
GO

CREATE TABLE dbo.DestObj1
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

CREATE TABLE dbo.DestObj2
(
    id int PRIMARY KEY ,
    name varchar(10),
    CreatedDate DATETIME DEFAULT GETDATE()
)

INSERT dbo.DestObj1
(
    id,
    name
)
SELECT 1,'test1'
UNION ALL 
SELECT 3,'test3'
GO

INSERT dbo.DestObj2
(
    id,
    name
)
SELECT 1,'test3'
UNION ALL 
SELECT 3,'test4'
GO


SELECT 
 'Sourcedb.dbo.SourceObj1' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj1
SELECT 
 'Destdb.dbo.DestObj1' as Objectname,
 * 
FROM Destdb.dbo.DestObj1
SELECT 
 'Sourcedb.dbo.SourceObj2' as ObjectName,
 * 
FROM Sourcedb.dbo.SourceObj2
SELECT 
 'Destdb.dbo.DestObj2' as ObjectName,
 * 
FROM Destdb.dbo.DestObj2
GO

Let see the same data inserted,

Now I will create a ssis package with passing dynamic table name and other require details. But before that I need to populate one table with same details which we need require as argument to be passed in a batch file,

USE Maintenance
GO

CREATE TABLE DatasyncDetails
(
 Id int identity(1,1),
 SourceDatabase varchar(50),
 DestDatabase varchar(50),
 SourceSchema varchar(20),
 DestSchema varchar(50),
 SourceTable varchar(50),
 DestTable varchar(20),
)


INSERT INTO DatasyncDetails
(
SourceDatabase, 
DestDatabase, 
SourceSchema, 
DestSchema, 
SourceTable, 
DestTable
)

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj1',
 'DestObj1'
 
UNION ALL

SELECT 
 'SourceDB',
 'DestDB',
 'DBO',
 'DBO',
 'SourceObj2',
 'DestObj2'

SELECT 
 SourceDatabase, 
 SourceSchema, 
 SourceTable, 
 DestDatabase, 
 DestSchema, 
 DestTable
FROM Maintenance.dbo.DatasyncDetails
GO

Let's check the data populated which we have to process for discrepancies,

All required demo objects created to prepare for ssis package,

and the variables used ,

I have presented two whole snaps with details of whole flow, Now we will the all the steps one by one.

Step 1 : Execute SQL Task

Step 2 : For Each Loop Container

Step 3 :  Execute Process Task

Here we will create a batch file and call this task where arguments pass from parameter we mapped in an earlier task. You can see the content of batch file and the argument used inside,

"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" 
–sourceserver [PARESH\MSSQLSERVER2012]
-sourcedatabase [%1]
-sourceschema [%2]
-sourcetable [%3]
-sourceuser [dba]
-sourcepassword [dba@1234]
-destinationserver [PARESH\MSSQLSERVER2012]
-destinationdatabase [%4]
-destinationschema [%5]
-destinationtable [%6]
-destinationuser [dba]
-destinationpassword [dba@1234]
-et Difference
-f C:\DiffOutput\%7

Now I will use this batch file in execute process task and the use the arguments passed by For Each Loop container.

Expression of Arguments :

@[User::SourceDB]  +" "+  @[User::SourceSchema] + " " +  @[User::SourceTable] +" "+  @[User::DestDB]  +" "+  @[User::DestSchema] + " " +  @[User::DestTable] +" "+ @[User::SourceTable]

Finally done with all the steps and will have to run the package and will review the resulted SQL script log for discrepancies. So let's run it and review the out files.
Running…

Output files,

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj1]
UPDATE [DBO].[DestObj1] SET [CreatedDate]='2012-10-06 11:39:10.620' WHERE [id] = 1
INSERT INTO [DBO].[DestObj1] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.620',2,'test2')
DELETE FROM [DBO].[DestObj1] WHERE [id] = 3

-- Host: PARESH\MSSQLSERVER2012
-- Database: [DestDB]
-- Table: [DBO].[DestObj2]
UPDATE [DBO].[DestObj2] SET [CreatedDate]='2012-10-06 11:39:10.627' WHERE [id] = 1
INSERT INTO [DBO].[DestObj2] ([CreatedDate],[id],[name]) VALUES ('2012-10-06 11:39:10.627',2,'test4')
DELETE FROM [DBO].[DestObj2] WHERE [id] = 3

We can use more tables to find differences between them as we did. Here you can use server name, username & password as an argument and make it fully dynamic, but if those SQL servers can be connect from there. In the next post I will add some additional task to apply differences automatically at destination servers\databases. Hope you will like and share it.