Invalid Column __$command_id issue during CDC implementation in SSIS package


 

In this blog, we will be addressing the problem with CDC implementation in SSIS package which goes with the error ‘invalid column name __$command_id’.

 

CDC stands for the change data capture. It was introduced in SQL server 2012. In SSIS package we have CDC control flow task and CDC source and CDC destination in Data flow task. CDC processing logic split into two packages – an Initial Load package that will read all of the data in the source table, and an Incremental Load package that will process change data on subsequent runs.

 

Issue-:

While previewing the CDC source task in an incremental package load, we get the error: ‘invalid column name __$command_id

 

Workaround-:

The profiler reveals calls to the function [cdc].[fn_cdc_get_all_changes_DimCustomer_CDC]. Here DimCustomer_CDC is the CDC enabled table, that we select in CDC source control. One catch is here that during CDC source task setup we select the processing mode it could be net or all. The function name depends on this processing mode. So function name could be [cdc].[fn_cdc_get_all_changes_ DimCustomer_CDC] or [cdc].[fn_cdc_get_net_changes_ DimCustomer_CDC].

 

Adding the column __$command_id in the function definition should resolve this issue. The following T-SQL script will update all the functions that are related to CDC instance of any table. We need to run the below query on CDC enabled database.

 

sp_msforeachtable N'declare @tsql nvarchar(max), @fn_all sysname, @fn_all_bk sysname, @fn_net sysname, @fn_net_bk sysname, @supports_net_changes bit

if object_id(N''cdc.change_tables'') is null return

print N''Verifying object ?...''

SELECT @fn_all = N''fn_cdc_get_all_changes_'' + ct.capture_instance,

@fn_all_bk = N''[cdc].[fn_cdc_get_all_changes_'' + ct.capture_instance + N''_original]'',

@fn_net = N''fn_cdc_get_net_changes_'' + ct.capture_instance,

@fn_net_bk = N''[cdc].[fn_cdc_get_net_changes_'' + ct.capture_instance + N''_original]'',

@supports_net_changes = ct.supports_net_changes FROM cdc.change_tables ct WHERE ct.source_object_id = object_id(N''?'')

if @fn_all is null or object_id(N''[cdc].[''+@fn_all+N'']'') is null begin

print N''Workaround was not required for this table (is not enabled for CDC).''

end else if object_id(@fn_all_bk) is null begin

print N''Workaround was not applied for this table.''

end else begin

if object_id(N''[cdc].[''+@fn_all+N'']'') is not null begin

print N''Removing proxy function ''+@fn_all

set @tsql = N''drop function [cdc].[''+@fn_all+N'']''

exec (@tsql)

end

print N''Renaming function populating all changes back to original name''

exec sp_rename @fn_all_bk, @fn_all

if @supports_net_changes = 1 and object_id(@fn_net_bk) is not null begin

if @fn_net is not null and object_id(N''[cdc].[''+@fn_net+N'']'') is not null begin

print N''Removing proxy function ''+@fn_net

set @tsql = N''drop function [cdc].[''+@fn_net+N'']''

exec (@tsql)

end

print N''Renaming function populating all changes back to original name''

exec sp_rename @fn_net_bk, @fn_net

end

end'

go

 

Please reach-out to the SQL Server Integration services support team if you are still facing any issues.

 

DISCLAIMER:

Any sample code or query is provided for the purpose of illustration only and is not intended to be used in a production environment. ANY SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

 

Author:      Umakant Khandekar  – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Chetan KT - Technical Advisor, SQL Server BI Developer team, Microsoft

 

Comments (0)

Skip to main content