SSIS CDC Control task with Azure SQL Database

Question

Hi All,
I got an error on using SSIS CDC control task. The database which is enable CDC is Azure SQL database.

Once connecting, I got the error

Reference to database and/or server name in 'master.sys.databases' is not supported in this version of SQL Server. (.Net SqlClient Data Provider)

From what I understand from the message, azure sql db doesnt support query information from master.sys.databases table as SQL server 2019. Does anyone face this issue before or have any idea with this issue.
Many thanks.

Answer 1

Hi @Chinh Phan ,

Azure SQL database doesn't support USE database or across database operations directly, even these databases are in the same Azure SQL Server.

Azure SQL database only support the across database query with elastic query. That still need many steps to achieve the across query. It's not supported to create table across the database. We must create a new connection(query session) on other databases.

Just for now, it's unsupported and we can not make this work.

In addition, cross-database and cross-instance queries using three or four part names. Three part names referencing the tempdb database and the current database are supported. Elastic query supports read-only references to tables in other MSSQL databases.

Please refer to transact-sql-tsql-differences-sql-server.

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Chinh Phan ,

CDC is now available in public preview in Azure SQL, enabling customers to track data changes on their Azure SQL Database tables in near real-time. Now in public preview, CDC in PaaS offers a similar functionality to SQL Server and Azure SQL Managed Instance CDC, providing a scheduler which automatically runs change capture and cleanup processes on the change tables. These capture and cleanup processes used to be run as SQL Server Agent jobs on SQL Server on premises and on Azure SQL Managed Instance, but now they are run automatically throughout the scheduler in Azure SQL Databases.

CDC is only supported on Standard 3+ databases; in case you enabled it on DBs lower than S3, please either disable CDC or upgrade your DB to a higher tier.

See here Introducing Change Data Capture for Azure SQL Databases (Public Preview)

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

hi @ZoeHui-MSFT , thanks for your reply.
And yes, I am aware of the supported version and I am using Azure SQL DB Standard S3: 100 DTUs at the moment. I also tried to upgrade the DB to S4 as well but no help though. The fact that we could enable CDC in the DB and at table level, changed records have been populated into cdc.TableName_CT but error happened when using ssis cdc control component (with the lower version like S0,S1, S2, the issue will happen at the enable CDC step or another error message will happen, something like Database xxxis not CDC enabled.)