SSIS Package to get Table Schema Discrepancy using XML

SSIS Package to get Table Schema Discrepancy using XML.

I have created package which uses XML in Execute SQL Task and compares two table’s schema discrepancy.

You can use this script to compare table in different instances with different database or in same instance with difference database or in same instance with same database.

You can save your result in database table in any text file, here i have messaged the result.

I have done this stuff in different instances with different database.

Please flow the step to do so.

Step1 : Create one sample table with one column difference on two difference instnace.

Table script for source connection

CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [PhysicalName] [varchar](200) NULL,
    [DBSize] [int] NULL)

Table script for destination connection


CREATE TABLE [dbo].[DatabaseInfo]

(
    [ServerName] [varchar](20) NULL,
    [DatabaseName] [varchar](20) NULL,
    [LogicalName] [varchar](20) NULL,
    [DBSize] [int] NULL
)

here, i have deleted column PhysicalName to get an idea.

Step2: Now drag two Execute SQL Task, One Script Task, two OLEDB Connection and two variables.

TableSchemaDifference 1

Variables

Obj_Result –> DataType Object

XMLData –> String

Step3 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> XML

–>SQL Statement


SELECT
 CLMList.Table_Name,
 CLMList.COLUMN_NAME
FROM Information_Schema.Columns CLMList
Where CLMList.Table_Name = 'DatabaseInfo'
For XML Path('SyncTables'),root('Root')

TableSchemaDifference 2

Step4 : Click on ResultSet –> Edit.

–> Select XMLData variable.

TableSchemaDifference 3

Click OK

Step5 : Right click on Source Execute SQL Task –> Edit.

–> Select ResultSet –> Full Result Set

–>SQL Statement


Declare @TableName xml
set @TableName = ?

SELECT
    SyncTables.TableName,
    SyncTables.ColumnName
FROM Information_Schema.Columns CLMList
    right join (SELECT p.value('(./Table_Name)[1]', 'VARCHAR(8000)') as TableName,
    p.value('(./COLUMN_NAME)[1]', 'VARCHAR(8000)') as ColumnName
FROM @TableName.nodes('//Root/SyncTables') t(p)) as SyncTables
    on CLMList.Table_Name = SyncTables.TableName
    and CLMList.Column_Name = SyncTables.ColumnName
 Where CLMList.Column_Name is null

TableSchemaDifference 4

Step6 : Click on Parameter Mapping  of Execute SQL Task Editor.

Select User::XMLData as input variable.

TableSchemaDifference 5

Step7 : Click on Result Set of Execute SQL Task Editor.

Select User::Obj_Result in Variable Name.

TableSchemaDifference 6

Step8 : Now Right click on Script Task –> Edit.

Select User::Obj_Result as ReadOnlyVariables.

TableSchemaDifference 7

Click on Edit Script and past below code.

</pre>
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
#endregion

namespace ST_e81be99c144345b2bb202cbd5460cb01
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
   // TODO: Add your code here
            try
            {
                OleDbDataAdapter OA = new OleDbDataAdapter();
                DataTable DT = new DataTable();
                DataRow DR;

               
                OA.Fill(DT, Dts.Variables["User::Obj_Result"].Value);
                foreach (DataRow row in DT.Rows)
                {
                    DR = row;
                    string TableName, ColumnName;
                    TableName = DR["TableName"].ToString();
                    ColumnName = DR["ColumnName"].ToString();

                    MessageBox.Show("Table Name : " + TableName.ToString() + " Column Name : " + ColumnName.ToString());
                }

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Now run the package and it will show you column difference in popup message.

Regards,

Nirav Gajjar

SSIS Package Foreach Loop with ADO Connection

SSIS Package Foreach Loop with ADO Connection.

In my last blog i had written SSIS Package Foreach Loop with File Connection(Link: http://nirav.extreme-advice.com/2013/02/25/ssis-package-foreeach-loop-with-file-connection/).

This package gives you same thing, variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task) but here i have used ADO Connection as enumerator in behalf of File Connection.

Step1 : Drag

–> Execute SQL Task to get list of values from database table and import to object variable

–> Foreach Loop Container.

–> Script Task.

ForEachLoopWithADO1

Step2 : Create Variables.

–> Obj_Result – Data Type Object.

–>ServerName – Data Type String.

–> DataSource – Data Type String.

ForEachLoopWithADO2

Step3 : Right Click on Execute SQL Task –> Edit.

–> Result Set – Full Result Text.

–> Connection – Your local database connection.

–> SQL Statement – Select Name,Data_Source From Sys.Servers.

ForEachLoopWithADO3

Step4 : Select Result Set from your left pane of Execute SQL Task Editor.

–> Result Name : 0 (Zero).

–> Variable Name : User::Obj_Result.

ForEachLoopWithADO4

Step5 : Right click on Foreach Loop Container –> Edit.

–> Select Foreach ADO Enumerator.

–> User::Obj_Result.

ForEachLoopWithADO5

Step6 : Select Variable mapping from your left pane of Foreach Loop Editor.

–> Select User::ServerName with Index 0.

–> Select User::DataSource with Index 1.

ForEachLoopWithADO6

Step7 : Rigth click on Script Tak –> Edit.

–> ReadOnlyvariables – Select Both Variable shown in below figure.

ForEachLoopWithADO7

Step8 : Click on Edit Script and paste below code.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f67917be72d748f4a19778c4b647dbf0
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            string ServerName, DataSource;
            ServerName = Dts.Variables["User::ServerName"].Value.ToString();
            DataSource = Dts.Variables["User::DataSource"].Value.ToString();
            MessageBox.Show("Server Name : " + ServerName + " Data Source : " + DataSource);
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

This script will show you how to use variables and how to map variables between Execute SQL Task to Foreach Loop Container and Foreach Loop Container to Inner Script Task.

Regards,

Nirav Gajjar

SSIS Package Foreach Loop with File Connection

SSIS Package Foreach Loop with File Connection.

This package gives you an idea to get file details and variable mapping between Foreach Loop Container and any inner task contains in loop (here inner task is Script Task).

Step1 : Drag Foreach Loop and script task from SSIS Toolbox.

ForEachLoopWithFile1

Step2 : Create one variable with name “FileName” and datatype “string”.
this varialbe maps filename from Foreach Loop Container value to Script task.

Step3 : Right click on Foreach Loop Container –> Edit
On Collection pane –> select Foreach File Enumerator in Enumerator field.
choose your folder by clicking on browse button.
–> Select file extention if you want to filter file extention otherwise put *.* to get all files.
–> Select Name and Extention in Retrieve file name.
ForEachLoopWithFile2

Step4 : Select Variable mapping pane on Foreach Loop Editor.
–> Select User::FileName variable.
–> and Index to 0.
ForEachLoopWithFile3

Step5 : Right Click on Script Task –> Edit.
–> Select Script Pane –> Select User::FileName as ReadOnlyVarialbe.

Step6 : Click on Edit Scrit to write script code.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_f1f3822404bc4e8d9a22fbe2749ff1d9
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            string FileName;
            FileName = Dts.Variables["User::FileName"].Value.ToString();
            MessageBox.Show(FileName);
			Dts.TaskResult = (int)ScriptResults.Success;
		}

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Regards,
Nirav gajjar