What will happen when NOT FOR REPLICATION is specified on a constraint

Problem: What will happen when NOT FOR REPLICATION is specified on a constraint

Solution:

If a table is published using replication, foreign key constraints are automatically disabled for operations performed by replication agents. When a replication agent performs an insert, update, or delete at a Subscriber, the constraint is not checked; if a user performs an insert, update, or delete, the constraint is checked. The constraint is disabled for the replication agent because the constraint was already checked at the Publisher when the data was originally inserted, updated, or deleted.

Example:

In this article, I am going to show we can create a foreign key constraint with not for replication.

CREATE TABLE dbo.t1 (

c1 INT NOT NULL PRIMARY KEY

);

CREATE TABLE dbo.t2 (

c1 INT NOT NULL IDENTITY PRIMARY KEY,

c2 INT NOT NULL);

GO

ALTER TABLE dbo.t2 WITH CHECK

ADD CONSTRAINT FK_t2_t1 FOREIGN KEY (c2)

REFERENCES dbo.t1(c1)

NOT FOR REPLICATION

GO

OUTPUT:

Note: After NOT FOR REPLICATION  is specified it will convert the constraint is converted into to no check and it will not check for existing data in the table, which makes it not trusted.

SELECT [name], type_desc, is_disabled, is_not_trusted,is_not_for_replication

FROM sys.foreign_keys

WHERE parent_object_id = OBJECT_ID('dbo.t2');

GO

OUTPUT:

name	type_desc	is_disabled	is_not_trusted	is_not_for_replication
FK_t2_t1	FOREIGN_KEY_CONSTRAINT	0	1	1

Read More

How to shred object variable from a record set destination to act as data source for a destination.

Problem: How to shred object variable from a record set destination to act as data source for a destination.

Solution:   Creating a Source with the Script Component

If you store the result set of dataflow task inside a record set destination the result will be stored inside an object variable in the run time and to access this a source for another dataflow task for complex sources or temporary sources, we have an option of script component to send the object variable as source.

To know more about how to use a record set destination please go through the following article.

Record Set Destination

Script component can act as source, destination or transformation inside a dataflow task and to read this from a variable we must select the variable inside the read-only variables section.

Example:

In the example, I would like to show we can store data using an object type of variable inside of data flow and it creates the table during run time.

Step 0: Create a package and in the control flow add Data flow task and create a variable with object type select an  ole db source as source and record set destination as destination and map the variable in the destination variable area.

Step 1: Create another dataflow task and drag drop the script component from the transformations and select as source.

Step 2: Edit the Script component task and select the variable in the read-only area.

Step 3: Add Output columns to reference the result of the object variable. Alternatively, you can use an execute sql task and select the variable in the result set section and result type full to generate the output of variable.

Step 4: Edit the Script to read the variable and loop through the dataset of the variable to send a table result set. After editing the script build the solution to make sure no errors are generated.

After hitting edit script there will be a new window and copy the below code to replace the content in the window and build the solution, note this is in C#.

// Add in the appropriate namespaces

using System.Data;

using System.Data.OleDb;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void CreateNewOutputRows()

    {

        // Set up the DataAdapter to extract the data, and the DataTable object to capture those results

        OleDbDataAdapter da = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        // Extract the data from the object variable into the table

        da.Fill(dt, Variables.Recordset);

        // Since we know the column metadata at design time, we simply need to iterate over each row in

        //  the DataTable, creating a new row in our Data Flow buffer for each

        foreach (DataRow dr in dt.Rows)

        {

            // Create a new, empty row in the output buffer

            OutputBuffer.AddRow();

            // Now populate the columns

            OutputBuffer.Col1 = (dr["Col1"].ToString());

            OutputBuffer.Col2 = int.Parse(dr["Col2"].ToString());

            OutputBuffer.Col3 = int.Parse(dr["Col3"].ToString());

        }

    }

}

Step 5: Select a destination and map the output of the script component to a destination table.

Output:

As we can see from the preview the variable is acting as source and sending the result to destination.

Read More

How to store data inside a variable in SSIS.

Problem: How to store data inside a variable in SSIS.

Solution:   Recordset Destination

The Recordset destination creates and populates an in-memory ADO recordset. The shape of the recordset is defined by the input to the Recordset destination at design time.

Example:

In the example, I would like to show we can store data using an object type of variable inside of data flow and it creates the table during run time.

Step 0: Create a package and in the control flow add Data flow task and create a variable with object type.

Step 1: Edit the data flow task and select an ole db source and with the connection managers connect to a db and select a table.

Step 2: for the destination piece use a record set destination and from the drop down select the variable created in step 0 for read write and in the input output columns select the columns you chose to send as input.

Step 3: execute the package.

Output:

In the next article let’s see how we can use this variable as a source to call outside of this data flow.

Read More

How to combine different tables data into a destination table using SSIS?

Problem: How to combine different tables data into a destination table using SSIS?

Solution:   Union All Transformation

The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.

Note: Unlike merge join union all does not need sorted inputs.

Example:

In the example, I would like to show we can use different source tables to combine and send the output into a single table.

Step 0: Create a new package and in the control flow drag a data flow task from tool box.

Step 1: Drag and drop two Ole DB Sources and call one as table A and another as a table B.  Next go to transformations and pull Union all transformation to combine both the tables.

  

Step 2: Edit the union all to map the inputs of each columns.

Step 3: execute the package.

Read More

How to validate a destination table with a source table data using SSIS?

Problem: How to validate a destination table with a source table data using SSIS?

Solution:   Merge Join Transformation

The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin.

You can configure the Merge Join transformation in the following ways:

·         Specify the join is a FULL, LEFT, or INNER join.

·         Specify the columns the join uses.

·         Specify whether the transformation handles null values as equal to other nulls.

Note: merge join needs sorted inputs.

Example:

In the example, I would like to show we can use a Merge join to insert rows into a destination table based on new entries in the source table.

Step 0: Create a new package and in the control flow drag a data flow task from tool box.

 Step 1: Drag and drop two Ole DB Sources and call one as source table and another as a destination table.  Next go to transformations and pull sort transformation to sort the source table and do the same for destination table source and in the sort transformation select the column you would like to join both source and destination.

Step 2: Use Merge Join transformation and connect both the tables with inner, left or full based on need and select columns to be joined and output.

Step 3: Conditional split helps to separate the new rows inside by identify the nulls when left join is specified.

Step 4: map to the destination table and execute  the package.

Output:

Sent only the new rows.

Read More

How to Execute or call a package from another package in SSIS?

Problem: How to Execute or call a package from another package in SSIS?

Solution:   Execute Package Task

Inside the control flow we can create a task for calling a package to execute or reference child packages inside a parent package to run a pre-requisite package for the load before it runs.

The Execute Package task extends the enterprise capabilities of Integration Services by letting packages run other packages as part of a workflow.

You can use the Execute Package task for the following purposes:

·         For loading data into a star schema, you can build a separate package to populate each dimension and the fact table.

·         build a data extraction module that can be called from different packages. Each package that calls the extraction module can perform different data scrubbing, filtering, or aggregation operations.

·         The parent package runs the accessory packages, and based on the success or failure of the accessory packages, the parent package either commits or rolls back the transaction.

·         Controlling package security. Package authors require access to only a part of a multipackage solution. By separating a package into multiple packages, you can provide a greater level of security, because you can grant an author access to only the relevant packages.

A package that runs other packages is generally referred to as the parent package, and the packages that a parent workflow runs are called child packages.

Example:

In the example, I would like to show we can reference a child package which is executed from a parent package.

Step 0: Create a new package called parent package.

Step 1: Drag and drop the execute package and edit the package properties to reference the package from the properties like below and from the drop down select the child package you want to reference.

Step 2: Execute the package from the parent package and this will open the child package and execute it from the parent package.

Output:

As you can see this will open and execute the child package.

Read More

How to Debug a variable flow and watch the variable as it passes through different tasks in SSIS?

Problem: How to Debug a variable flow and watch the variable as it passes through different tasks in SSIS?

Solution:   Debugging Control Flow

In a troubleshooting process of SSIS packages to know what it’s compiling and run time values of variable we need to set break points on the specific tasks to watch the variable at the time of entry and out of the task.

When you launch SSIS, in the designer you can see or enable break points by right click anywhere in the design area. Or you can go to the designer tab and navigate through the windows to get to the break points and Add a new break point from there. Another quick way of watching the breakpoints from the designer is to press(CTRL+ALT+B).

you can enable a break point on the component level or task level. So, what happens in when you add a break point on the task there are few conditions we can choose to break depending on the selection it will pause executing when that condition hits and gives you time to watch the variable at that moment it entered the task by stepping inside the break point.

After we enable the break point on the task or component the designer will show a red dot on the task or component to indicate where the break point is set.

Below is the list of options you get when you want to break on the task.

Example:

In the example, I would like to show we can watch a variable inside of for loop each time it executes the variable tries to increment so it is something changes during run time.

To create a for loop please go through this article. for loop

Step 0: Add Break point on the for loop at the component and select pre execute and loop execution as below image.

Select options for breaking the task.

After adding break point you should be seeing a red dot on the container like below.

Step 1: Change the solution configuration to debug and then execute task or package.

Step 2: you should see the package stops executing on the for loop container as soon as hits the pre execution and open the break points options whether to watch or continue

Step 3: open the watch window and select the variable to watch and expand the arrow to watch values for each execution hit continue to see until it exits the loop.

Output:

As you can see the variables value is watched inside the watch window.

Read More