SSIS 2012-Configurations option is missing in MENU. Here is the solution.

Unlike SSIS 2008 , the package configuration option is missing in SSIS 2012 menu and in control flow(right click options).

But we can find the option in the following way.
1. Right click on the control flow tab.

2. Click on the properties

Yes… :)

There you can find…. option called as “Configurations”.

Let me know if you have any issues.

Thank you, I hope you enjoyed :)

 

 

 

 

 

Change Data Capture (MS SQL SERVER 2008)

The ability to detect the changed data in source systems and capture these changes is called Change Data Capture (CDC).

CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.

When we apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of the change in the database table row.

If we have n columns in our table then mirror of the tracked table will have n columns including the metadata columns like operation.

–Enabling the CDC for Database
USE DBNAME
GO
EXEC sys.sp_cdc_enable_db
GO
–Enabling the CDC for table
EXEC sys.sp_cdc_enable_table
@source_schema = N’yourschema’,
@source_name = N’yourtable’,
@role_name = NULL
GO

Let us create one table (test) with 2 columns (id, name) and enable CDC feature. Then the tracking table structure looks like follows.

Image

Step 1. Inserting the values into test table

Image

Step 2. Updating the name from  “abc” to “xyz”Image

Step 3. Delete the record Image

Step 4. Inserting new record with id as 2 and name as “def”Image

In the above table we can able to see “_$operation” column having values 1,2,3,4. Their denomination as follows.

Delete Statement = 1, Insert Statement = 2, Value before Update Statement = 3, Value after Update Statement = 4.

Like the above cases, we can do tracking for different tables whose need tracking.

In addition to the above table, there are other 5 tables to store ddl history, indexes for change tables, etc., will come when the CDC feature enabled.

Please feel free to add your comments/suggestions to share the knowledge.