Query to check Integration failed jobs error message

Query to check Integration failed jobs error message.

Recently i have used SSIS package and deployed this package to one of SSIS server. now i have created a scheduled job to execute this package for time intervals. After some execution job failed. As per our regular terms i have checked job’s history to get error message, but some how its not giving me a correct message.

Error Message from Job History.

Executed as user: DomainName\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:08:00 AM Package execution on IS Server failed. Execution ID: 3403914, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 2:08:00 AM Finished: 2:09:05 AM Elapsed: 64.99 seconds. The package execution failed. The step failed.

Before this deployed version if SSIS, i am  using filesystem package method and if job fails in this filesystem deployment task it shows me the error correctly. Now how can i get the error for deployed package jobs.

Solution : i have created a query to get error message for last failed schedule for particular job.

DECLARE @Full_ssis_command VARCHAR(4000),
 @Job_step_id INT,
 @Package_name VARCHAR(4000),
 @Tablehtml NVARCHAR(MAX),
 @Mailsubject VARCHAR(200),
 @Job_name VARCHAR(100),
 @Job_id UNIQUEIDENTIFIER

SELECT @Job_id = Job_id
FROM MSDB.DBO.Sysjobs
WHERE Name = 'JobName'

SELECT TOP 1 @Job_step_id = Step_id
FROM MSDB.DBO.Sysjobhistory (NOLOCK)
WHERE Run_status <> 1
 AND Step_id > 0
 AND Job_id = @Job_id
ORDER BY Instance_id DESC

SELECT @Full_ssis_command = Command
FROM MSDB.DBO.Sysjobsteps (NOLOCK)
WHERE Job_id = @Job_id
 AND Step_id = @Job_step_id
IF @Full_ssis_command LIKE '%.dtsx%'
 BEGIN
 SELECT @Package_name = RIGHT(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-), Charindex('\', Reverse(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-1)))-1) + '.dtsx''
 END

SELECT [Message_time],
 [Extended_info_id],
 [Package_name],
 [Message_source_name],
 [Subcomponent_name],
 [Package_path],
 [Execution_path],
 LEFT([Message], 400)
FROM SSISDB.[catalog].[Event_messages] (NOLOCK)
WHERE [Package_name] = @Package_name
 AND Event_name = 'OnError'
 AND Message_time >= DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
 AND Operation_id IN (SELECT Max(Operation_id)
 FROM SSISDB.[catalog].[Event_messages](NOLOCK)
 WHERE [Package_name] = @Package_name)
ORDER BY Message_time ASC

Thanks & Regards,
Nirav Gajjar

Create tables schema to another database using SMO

Create tables schema to another database using SMO.

I have created a package which will create table script to your physical path and also executes same thing on another database to create table schema.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Collector;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Collections.Generic;
#endregion

namespace ST_ab378c7f30204c94a2f49a2b0f7ac500
{
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
		public void Main()
		{
            try
            {
                string TableName;
                TableName = (Dts.Variables["User::TableName"].Value.ToString());
                Server server = new Server();
                Database source_database = new Database();
                Database destination_database = new Database();
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = "sa";
                server.ConnectionContext.Password = "reliancesp";
                server.ConnectionContext.ServerInstance = "User8\\SQL";
                source_database = server.Databases["Adventureworks2012"];
                destination_database = server.Databases["ReceiverDB"];

                Table table = source_database.Tables[TableName];
                string tables = table.Name.ToString();

                //Define properties in scriptingOptions
                ScriptingOptions scriptingOptions = new ScriptingOptions();
                scriptingOptions.ClusteredIndexes = true;
                scriptingOptions.NonClusteredIndexes = true;
                scriptingOptions.Default = true;
                scriptingOptions.DriAll = true;
                scriptingOptions.Indexes = true;
                scriptingOptions.IncludeHeaders = true;
                scriptingOptions.ScriptSchema = true;
                scriptingOptions.ScriptDrops = false;
                scriptingOptions.Triggers = true;

                StringCollection result = table.Script(scriptingOptions);

                var script = "";
                foreach (var line in result)
                {
                    script += line + "\r\n";
                }

                System.IO.StreamWriter fs = System.IO.File.CreateText(@"D:\data\" + TableName + ".sql");
                fs.Write(script);
                fs.Close();
                destination_database.ExecuteNonQuery(script);
            }
            catch(Exception e)
            {
                MessageBox.Show(e.Message.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
		}

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

	}
}

Deploy SSIS Package in SQL Server 2012

Deploy SSIS Package in SQL Server 2012.

SQL Server 2012 added new feature of Integration Service Catalog, which makes SSIS package deployment task very easy.

To get advantage of this feature you must have sql server integration service installed on your server.

Follow the steps to deploy package.

Step1 : Create Integration Service catalog.

DeploySSISPackage1

Step2 : Enable CLR Integration and provide encryption password.

DeploySSISPackage2

Step3 : You will find SSISDB catalog will created. Creating catalog automatically creates database “SSISDB” to your instance.

DeploySSISPackage3

Step4 : Now go to your package right click on your project and select “Deploy”.

DeploySSISPackage4

Step5 : Selection will opens deployment window click on “next”.

DeploySSISPackage5

Step6 : Provide destination server detail to server name.

DeploySSISPackage6

Step7 :  Click on “Browse” to select Path:

DeploySSISPackage7

Create New folder with your desired name.

DeploySSISPackage8

Step8 : Click on “Next”.

DeploySSISPackage9

Step9 : Check the summary and click on “Deploy”.

DeploySSISPackage10

Step10 :  Click on “Close”.

DeploySSISPackage11

Step11 : Check your deployed package under Integration service in SSMS.

DeploySSISPackage12

Step12 : To Schedule this package create new job with name “Deployment Testing”.

DeploySSISPackage13

–> Select “SQL Server Integration Services Package’ in Type.

–> Select “SSIS Catalog” in Package Source and provide server connection details.

DeploySSISPackage14

–> Select your package path.

DeploySSISPackage15

–> Schedule the package as your desired time.

DeploySSISPackage16

 

Regards,

Nirav Gajjar

Schedule SSIS Package Without Deploying

Schedule SSIS Package Without Deploying.

You can schedule your package before you completely deploy it. you just need to build your SSIS Package and you will get executable “.dtsx” file.

Path for “.dtsx” file is different according to SQL Server versions.

For SQL Serve 2008 :  “your package path \bin\*.dtsx”

For SQL Serve 2012 :  “your package path \bin\Development\*.dtsx”

There are two ways to schedule SSIS Package if you have created package through “File System”.

1) SQL Server Agent Job.

You need to create new job in your SQL Server.

Step1 : Connect your local sql server –> expand SQL Server Agent –> right click on job and click on new job.

SSIS1

Step2 : Give “Call Package” to your job name.

SSIS2

Step3 : Click on “New” button on Steps tab.

SSIS3

Step4 : On New Job Step – Select SQL Server Integration Package in TYPE and File System in Package Source and give “.dtsx” file path.

SSIS4

Step5 : Schedule your package as you convenient time.

SSIS5

2) Windows Schedule Task.

You can’t call “.dtsx” file directly from Schedule task, you must need to create batch file which calls “.dtsx” file and letter you can create schedule task for that batch file.

Step1 : Create batch file with following code.


"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /FILE "D:\Work\SSIS\Nirav's Diary\Nirav's Diary\bin\Development\GetSpaceInfoWithWMI.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF  /REPORTING EWCDI

save it to “D:\Work\SSIS\BatchFiles\GetSpaceInfoWithWMI.bat”

Step2 : Create Windows Schedule Task.

Click on Start button — > Run –> and Type — > control schedtasks and click ok.

Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.

Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.

Give schedule name  and select option “Run whether user is logged in or not”.

SSIS6

Step4 :  Select Trigger tab from Create Task window –> click on new to schedule time.

SSIS7

Step5 : Select Action tab from Create Task window –> click on browse to map file path.

SSIS8

Click on “OK” button to complete it. windows will ask for user name and password.

Please apply administrator user and password or any other user which have a permission to execute schedule task.

Regards,

Nirav Gajjar

SSIS Package to generate MailProfile script

SSIS Package to generate MailProfile script.

There is no provision to generate script for mail profile in sql server. but i have one script that can be used to create mail profile, mail account and map them together.

I am using this script whenever i needed it, letter i have an idea to start scripting automatically of mail profile and i have created one package that genrates all three scripts and store it in one sql file. this is very helpfull for disaster recovery planning for script backup.

Step1 : To create package you need one Data Flow Task, One Flat File Connection, One OLEDB Connection, and one variable
Please see in figure.

BackupMailProfileScript 1

Step2 : Drag one OLE DB Source and one Flat File Destination. Right click on Data Flow Task –> Edit

BackupMailProfileScript 2

Step3 : Right click on OLEDB Source Editor — > Edit

Select your local machine connection.

Select SQL Command in Data Access mode.

Paste Below Code.


Select '--Create Account' AS CMD
Union All
SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ''' + m.name + ''',
@description = ''' + m.description + ''',
@email_address = ''' + m.email_address + ''',
@replyto_address = ''' + m.replyto_address + ''',
@display_name = ''' + m.display_name + ''',
@mailserver_name = ''' + s.servername + ''',
@mailserver_type = ''' + s.servertype + ''',
@port = ''' + cast(s.port as nvarchar) + ''',
@username = ''' + isnull(c.credential_identity,0) + ''',
@password = ''x'',
@use_default_credentials = 0,
@enable_ssl = 0' AS CMD
From msdb.dbo.sysmail_account m
LEFT OUTER JOIN msdb.dbo.sysmail_server s
ON m.account_id = s.account_id
LEFT OUTER JOIN master.sys.credentials c
ON s.credential_id = c.credential_id
Union All
Select '--Create Profile' AS CMD
Union All
SELECT '
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ''' + name + ''',
@description = ''' + isnull(description,'NULL')+ '''
' AS CMD
from msdb.dbo.sysmail_profile
Union All
Select '--Create Link For Profile to Account' AS CMD
Union All
Select '
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '''+ a.name +''',
@account_name = ''' + b.name + ''',
@sequence_number = 1 ' as CMD
from msdb.dbo.sysmail_profile as a
join msdb.dbo.sysmail_account as b on a.name = b.name

BackupMailProfileScript 3

Step4 : Click on Columns left side on OLE DB Source editor, configure column and click ok.

BackupMailProfileScript 4

Step5 : Right Click on Flat File Destination and select “Primary_Location” to flat file connection manager.

BackupMailProfileScript 5

Click on Mappings to map variable.

BackupMailProfileScript 6

Step6 : Now to make folder dynamic – select Primary_location from connection manager and hit F4.

BackupMailProfileScript 7

Click on Expression button.

BackupMailProfileScript 8
Copy below code to Expression.

@[User::FolderName]  + "<a href="file://mailprofile/">\\MailProfile</a>_" +  (DT_WSTR,4)YEAR(GETDATE())
+ RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2)
+ RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2) + ".sql"

Click OK and execute the package it will creates script to “D:\ScriptBackup\MailProfile\MailProfile_YYYYMMDD.Sql” this file contains three different bunch of script. Fist to create Mail Account Second to create Mail Profile. Third to map Mail Account to Mail Profile.

Regards,

Nirav Gajjar

SSIS Package to Backup Jobs, Operators and Alerts Script using SMO

SSIS Package to Backup Jobs Script using SMO.

This package willl use some SMO reference and generates SQL Server Jobs, Operators and Alerts script backup.

I had written the code for SQL Server Jobs only and commented code for Operators backup and Alerts Backup.

You just need to replcace Operators or Alerts instead of Jobs.

I have created SSIS package that automatically creates backup folder for current date and save your all jobs with jobsname.sql separately as well as all jobs in one file with static name AllJobsInOneFile.sql.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

BackupJobsScript 1

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0″ + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0″ + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 : Now Right Click on Script task and select all user variables to ReadOnlyvariables.

BackupJobsScript 2

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO

Microsoft.SQLServer.ManagedDTS

Microsoft.SQLServer.ScriptTask.

Paste below code to your script task code.


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
#endregion

namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {
  public void Main()
  {
            StringCollection sc = new StringCollection();
            ScriptingOptions so = new ScriptingOptions();
            so.IncludeDatabaseContext = true;

            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" + FolderDate.ToString());
            try
            {
                string script = "";

                string JobName;
                string AllJobsInOneFile = "";
                //Loop over all the jobs
                //To take a script backup of Operators.

                //foreach (Job J in srv.JobServer.Operators)
                //To take a script backup of Alerts.
                //foreach (Job J in srv.JobServer.Alerts)
                //To take a script backup of Jobs.
                foreach (Job J in srv.JobServer.Jobs)
                {

                    //Output name in the console
                    Console.WriteLine(J.Name.ToString());

                    JobName = J.Name.ToString();
                    sc = J.Script(so);

                    //Get all the text for the job
                    foreach (string s in sc)
                    {
                        script += s;
                    }

                    //Generate the file
                    TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + JobName.Replace(':', '_').ToString() + ".sql");
                    tw.Write(script);
                    tw.Close();
                    TextWriter WriteAllJobs = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + "AllJobsInOneFile.sql", true);
                    WriteAllJobs.Write(script + "\nGO\n");
                    WriteAllJobs.Close();
                    script = "";
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar

SSIS Package to Backup Replication Script using SMO

SSIS Package to Backup Replication Script.

Hi,

This package willl use some SMO reference and generates SQL Server replication backup script same as you do it manually like, Right click on Publication –> Generate Scripts….

I have created SSIS package that automatically creates backup folder for current date and save your all replication publication script with name “publication name + .sql”.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0” + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0” + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 :  Now Right Click on Script task and select all user variables to ReadOnlyvariables.

Replication Script Backup 1

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO.

Paste below code to your script task code.

#region Namespaces
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

#endregion</pre>
namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {       
  public void Main()
  {
            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" +FolderDate.ToString());
            ReplicationServer RS = new ReplicationServer(conn);
            try
            {
                foreach (ReplicationDatabase RD in RS.ReplicationDatabases)
                {

                    if (RD.HasPublications)
                    {
                        foreach (TransPublication TP in RD.TransPublications)
                        {
                            //TextWriter tw = new StreamWriter("D:\\" + TP.Name.ToString() + ".sql");
                            TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + TP.Name.ToString() + ".sql");
                            tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));
                            tw.Close();
                        }
                    }
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar

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 to Call Web Service

SSIS Package to Call Web Service.

You can Call WebService from SSIS package and transfers your data.

First of all you have to create web service with function as  you needed to call.

Step1 : Go To Microsoft Visual Studio –> New –> WebSite –> ASP.Net WebService and provide you web service name.

WebService 1

Step2 : Paste below code to your Service1.asmx.vb file.

I have created new function “MyHelloWorld” with parameter name in which i am passing a string to write in a destination file.


Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
' <System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="<a href="http://tempuri.org/">http://tempuri.org/</a>")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class Service1
    Inherits System.Web.Services.WebService
    <WebMethod()> _
    Public Function MyHelloWorld(ByVal name) As String
        Dim FILE_NAME As String = "E:\WebServiceTextFromService.txt"
        Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)

        objWriter.Write(name)
        objWriter.Close()
        Return "Process Completed."
    End Function

End Class

Step3 : Now Create New SSIS Package with your desired name.

Drag Weservice Task to Control Flow.

Create one variable “Result” with datatype string and value “I am writting this content to file by web service task”.

WebService 2

Step4 : Create one http connection manager. i am using 61508 port number for my local web service so my Server URL : http://localhost:61508.

This port number is assigned by default, you can change port number from web service property.

WebService 4

Step5 : Create one file connection to write output result to local path.

WebService 8

Step6 :  Right click on Web Service Task –> Edit.

On General Tab Select HTTP Connection Manger which you have created on Step4.

Select your WSDL file path to WSDLFile field.

WebService 5

Step7 : Select Input path of Web Service Task Editor.

Select your service name in Service and Method “MyHelloWorld” which you have created on Step2.

After selecting Method, Parameter field enables automatically.

Click on Variable checkbox and select User::Result in value .

WebService 6

Step8 : Select OutPut tab on left pane on Web Service Task Editor.

Select Flat File Connection “WebServiceText.txt” in File Field.

WebService 7

Step9 : After completing this build and run the project and you will get file will be created on “D:\WebServiceText.txt”

with the content “I am writting this content to file by web service task”.

 

Regards,

Nirav Gajjar