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

Published Jan 15 2019 06:03 PM 1,833 Views
First published on MSDN on May 14, 2018


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 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’









Solution:

  1. Ensure we are on the latest the latest version of SQL server .



  1. If the issue persists,



  • Download the Attunity dlls from the following site based on your SQL Server/SSIS version.


  • Place the dlls in the right location and GAC

    • We can find 4 dll in $\bin on the downloaded folder:

      • Attunity.SqlServer.CDCControlTask.dll

      • Attunity.SqlServer.CDCDesign.dll

      • Attunity.SqlServer.CDCSplit.dll

      • Attunity.SqlServer.CDCSrc.dll





  • You need to replace each of them to:

    • X86 folder

    • X64 folder





  • Further GAC them so that they appear on the GAC_MSIL folder






Detailed locations are here:





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







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

Reviewer: Krishnakumar Rukmangathan – Support Escalation Engineer, SQL Server BI Developer team, Microsoft

%3CLINGO-SUB%20id%3D%22lingo-sub-319179%22%20slang%3D%22en-US%22%3EInvalid%20Column%20__%24command_id%20issue%20during%20CDC%20implementation%20in%20SSIS%20package%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319179%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20May%2014%2C%202018%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20this%20blog%2C%20we%20will%20be%20addressing%20the%20problem%20with%20CDC%20implementation%20in%20SSIS%20package%20which%20goes%20with%20the%20error%20%3CEM%3E%20%E2%80%98invalid%20column%20name%20__%24command_id%E2%80%99%20%3C%2FEM%3E%20.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CDC%20stands%20for%20the%20change%20data%20capture.%20It%20was%20introduced%20in%20SQL%20server%202012.%20In%20SSIS%20package%20we%20have%20CDC%20control%20flow%20task%20and%20CDC%20source%20and%20CDC%20destination%20in%20Data%20flow%20task.%20CDC%20processing%20logic%20split%20into%20two%20packages%20%E2%80%93%20an%20%3CSTRONG%3E%20Initial%20Load%20%3C%2FSTRONG%3E%20package%20that%20will%20read%20all%20the%20data%20in%20the%20source%20table%2C%20and%20an%20%3CSTRONG%3E%20Incremental%20Load%20%3C%2FSTRONG%3E%20package%20that%20will%20process%20change%20data%20on%20subsequent%20runs.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3EIssue%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20While%20previewing%20the%20CDC%20source%20task%20in%20an%20incremental%20package%20load%2C%20we%20get%20the%20error%3A%20%E2%80%98invalid%20column%20name%20__%24command_id%E2%80%99%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68732iF179013C43F2DDB5%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Solution%3A%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EEnsure%20we%20are%20on%20the%20latest%20the%20%3CA%20href%3D%22https%3A%2F%2Fsqlserverbuilds.blogspot.in%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20latest%20version%20of%20SQL%20server%20%3C%2FA%3E%20.%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3COL%3E%3CBR%20%2F%3E%3CLI%3EIf%20the%20issue%20persists%2C%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FOL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EDownload%20the%20Attunity%20dlls%20from%20the%20following%20site%20based%20on%20your%20SQL%20Server%2FSSIS%20version.%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fattunity-patch.s3.amazonaws.com%2FMicrosoftSSISCDC%2F1.1%2Fsp01-1.1-MicrosoftSSISCDC%2FAttunitySSISComponentsCDC_DLLs.zip%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20SQLServer%202012%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fattunity-patch.s3.amazonaws.com%2FMicrosoftSSISCDC%2F2.0%2Fsp01-2.0-MicrosoftSSISCDC%2FAttunitySSISComponentsCDC_DLLs.zip%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20SQLServer%202014%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fattunity-patch.s3.amazonaws.com%2FMicrosoftSSISCDC%2F4.0%2Fsp02-4.0-MicrosoftSSISCDC%2FAttunitySSISComponentsCDC_DLLs.zip%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%20SQLServer%202016%20%3C%2FA%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EPlace%20the%20dlls%20in%20the%20right%20location%20and%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fdkkx7f79(v%3Dvs.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20GAC%3C%2FA%3E%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EWe%20can%20find%204%20dll%20in%20%24%5Cbin%20on%20the%20downloaded%20folder%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EAttunity.SqlServer.CDCControlTask.dll%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAttunity.SqlServer.CDCDesign.dll%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAttunity.SqlServer.CDCSplit.dll%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EAttunity.SqlServer.CDCSrc.dll%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EYou%20need%20to%20replace%20each%20of%20them%20to%3A%20%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EX86%20folder%3C%2FLI%3E%3CBR%20%2F%3E%3CLI%3EX64%20folder%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%3CUL%3E%3CBR%20%2F%3E%3CLI%3EFurther%20%3CA%20href%3D%22https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fdkkx7f79(v%3Dvs.100).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20GAC%20%3C%2FA%3E%20them%20so%20that%20they%20appear%20on%20the%20GAC_MSIL%20folder%3C%2FLI%3E%3CBR%20%2F%3E%3C%2FUL%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3EDetailed%20locations%20are%20here%3A%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEM%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68733iDBFAD31FDEB68C58%22%20%2F%3E%20%3C%2FEM%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Please%20reach-out%20to%20the%20SQL%20Server%20Integration%20services%20support%20team%20if%20you%20are%20still%20facing%20any%20issues%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Author%3A%20%3C%2FSTRONG%3E%20Chetan%20KT%20-%20Technical%20Advisor%2C%20SQL%20Server%20BI%20Developer%20team%2C%20Microsoft%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Reviewer%3A%20%3C%2FSTRONG%3E%20Krishnakumar%20Rukmangathan%26nbsp%3B%E2%80%93%20Support%20Escalation%20Engineer%2C%20SQL%20Server%20BI%20Developer%20team%2C%20Microsoft%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-319179%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20May%2014%2C%202018%20%26nbsp%3BIn%20this%20blog%2C%20we%20will%20be%20addressing%20the%20problem%20with%20CDC%20implementation%20in%20SSIS%20package%20which%20goes%20with%20the%20error%20%E2%80%98invalid%20column%20name%20__%24command_id%E2%80%99.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-319179%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECDC%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EIntegration%20Services(SSIS)%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 06:03 PM
Updated by: