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.
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')
Step4 : Click on ResultSet –> Edit.
–> Select XMLData variable.
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
Step6 : Click on Parameter Mapping of Execute SQL Task Editor.
Select User::XMLData as input variable.
Step7 : Click on Result Set of Execute SQL Task Editor.
Select User::Obj_Result in Variable Name.
Step8 : Now Right click on Script Task –> Edit.
Select User::Obj_Result as ReadOnlyVariables.
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
