Validate SSIS package on server

SSIS server validation

Untitled

 

  • Any change in database schema will break the associated SSIS package.
  • Generally each change is responsible to identify dependent components to be included in the change scope.
  • But what about any missed dependencies. E.g. Recent production issue.
  • Such broken SSIS can be identified in Pre-Prod only if pre-prod is well controlled and identical copy of prod environment.
  • Process is required to identify packages impacted by dependencies changes like database schema.
  • Proactive validation of SSIS package could help to identify the issue and reduce the system downtime.

High level architecture

Untitled2

 

The solution is capable of validating all ssis packages for sql server version 2008 on-wards. Instead of publishing the full source code below is the pseudo code and sql scripts to validate the packages.

  1. Create C# console base project which takes the server name you want to validate. The app calls the web service to validate the SQL server (validate SSIS packages on that server).
  2. Create web api project and expose following methods (Job Controller)
    1. Validate – start the validation process by creating SQL agent job dynamically on target server
    2. ValidationFinished – this will be invoked by job when validation is finished.
    3. GetValidationStatus – this will provide the SQL agent job status currently validating server

The Validate method works as follows..

  • Get the SQL Server version
private Version GetServerVersion(string serverName)
 {
 var connectionString = string.Format("Server={0};Database=master;Trusted_Connection=True;", serverName);
 using (var connection = new SqlConnection(connectionString))
 {
 connection.Open();
 return new Version(connection.ServerVersion);
 }
 }
  • Create interface IProcessor and implement it for sql server 2008 and 2012 as they both have different way to validate package
  • Based on server version get the eligible concrete implementation.
  • The validation engine will create the validation job on target sql server. for each proxy account, the script will create job step to trigger a network share package running under proxy account. (for details see createValidationJob.sql)
  • Each validation step gets the package scheduled under the current account and validate the package, populate the results back on central server. and in the end extract the job history and delete the job.)
  • The network deployed ssis package uses same engine to validate 2008 and 2012 via IProcessor since validation is done differently for both servers.

The common scripts used for both servers are here (download and rename doc to zip)

Common-Scripts

The SQL server 2008 validation is done via DTEXEC with /validate command (download and rename doc to zip)

SQl 2008-Scripts

The SQL 2012 validation is done via EXECUTE SSISDB.catalog.validate_project stored proc. (download and rename doc to zip)

SQL 2012 Scripts

The centralized server which keeps the status of SSIS validation looks like

Untitled3

The font end to display the data was build on angular 2 API..

@@servername

If @@servername returns null or return wrong host name, execute below mentioned sql Query.


DECLARE @Current SysName
Select @Current = Convert(varchar(128), SERVERPROPERTY(‘ServerName’) )
IF(@Current <> @@SERVERNAME)
BEGIN
EXEC sp_dropserver @@SERVERNAME
EXEC sp_addserver @server=@Current,@local = ‘local’
EXEC master..xp_cmdshell ‘ECHO NET STOP MSSQLSERVER > restartSQL.bat’, no_output
EXEC master..xp_cmdshell ‘ECHO NET START MSSQLSERVER >> restartSQL.bat’, no_output
EXEC master..xp_cmdshell ‘restartSQL.bat’, no_output
–Connection with sql break here
END

— Check if every thing is correct
SELECT @@Servername
SELECT SERVERPROPERTY(‘ServerName’)

Export data from corrupted database

Below is the sql script to import data from source database into target database, It is assumed that you have both the databases on single server.The source database is current database & few tables are corrupted whereas the traget database is created from old backup for target database.since their is corruption ,it is not possible to take backup of current database. This script imports back data (only) from source to target database. You need to only replace 3 lines of the script (12th line from bottom).
Download script from here

 

------------- Create helper functions -----------------------------

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mig_ImportTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mig_ImportTable]
GO

CREATE PROC dbo.Mig_ImportTable @Database SYSNAME, @Table SYSNAME AS
	SET NOCOUNT ON

	DECLARE @Column SYSNAME
	DECLARE @SQL NVARCHAR(4000)
	DECLARE @colSQL NVARCHAR(4000)
	DECLARE @IsIdentity BIT
	DECLARE @IsTableIdentity BIT

	SET @colSQL = ''
	SET @SQL=''
	SET @IsTableIdentity = 0 --false
	SET @IsIdentity = 0 --false

	PRINT 'Table Migration Started for :' + @Table + ' in ' + @Database

	DECLARE curMoveDown CURSOR
	LOCAL FORWARD_ONLY
	OPTIMISTIC FOR
	SELECT column_name,COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')as IsIdentity FROM information_schema.columns
	WHERE UPPER(table_name ) = UPPER(@Table)

	OPEN curMoveDown FETCH NEXT FROM curMoveDown INTO @Column,@IsIdentity
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @colSQL = @colSQL + '[' + @Column + '],'
		SET @IsTableIdentity = @IsTableIdentity | @IsIdentity
		IF (@IsTableIdentity = 1) BREAK
		FETCH NEXT FROM curMoveDown INTO @Column,@IsIdentity
	END
	CLOSE curMoveDown
	DEALLOCATE curMoveDown 

	IF(LEN(@colSQL)>1)
	BEGIN
		SET @colSQL = LEFT(@colSQL,LEN(@colSQL) - 1)

		IF(@IsTableIdentity = 1) SET @SQL = @SQL + 'SET IDENTITY_INSERT ' + @Table + ' ON '
		SET @SQL = @SQL + '	ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL '
		SET @SQL = @SQL + '	ALTER TABLE ' + @Table + ' NOCHECK CONSTRAINT ALL '
		SET @SQL = @SQL + '	TRUNCATE TABLE ' + @Table + '  '
		IF(@IsTableIdentity = 1)
			SET @SQL = @SQL + '	INSERT INTO ' + @Table + ' (' +  @colSQL + ') SELECT '+  @colSQL + ' FROM ' + '[' + @Database + '].[dbo].[' + @Table + '] '
		ELSE
			SET @SQL = @SQL + '	INSERT INTO ' + @Table + ' SELECT * FROM ' + '[' + @Database + '].[dbo].[' + @Table + '] '
		IF(@IsTableIdentity = 1) SET @SQL = @SQL + '	SET IDENTITY_INSERT ' + @Table + ' OFF '
		SET @SQL = @SQL + '	ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL '
		SET @SQL = @SQL + '	ALTER TABLE ' + @Table + ' CHECK CONSTRAINT ALL '

		EXEC sp_executesql @SQL
		PRINT 'Table migrated :' + @Table
	END
	ELSE
		PRINT 'No Column found :' + @Table + ' (SQL = ' + @colSQL + ')'

	PRINT 'Table Migration finished for :' + @Table
GO

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[Mig_ImportDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mig_ImportDatabase]
GO

CREATE PROC dbo.Mig_ImportDatabase @Source SYSNAME AS	

	DECLARE @SERVER SYSNAME
	DECLARE @INSTANCE SYSNAME
	DECLARE @FULLNAME SYSNAME
	DECLARE @DBNAME SYSNAME
	DECLARE @cmd varchar(1000)
	DECLARE @SQL Nvarchar(1000)
	DECLARE @Table Nvarchar(100)
	DECLARE @Result INT

	SET NOCOUNT ON

	CREATE TABLE [#Mig_FailedTables] ( [name] SYSNAME NOT NULL ) ON [PRIMARY]

	SELECT @SERVER = CONVERT(SYSNAME, SERVERPROPERTY('servername'))
	SELECT @INSTANCE = IsNull('',CONVERT(SYSNAME, SERVERPROPERTY('InstanceName')))
	SELECT @DBNAME = DB_NAME()

	IF(Len(@INSTANCE)>0) SET @FULLNAME = @SERVER + '' + @INSTANCE
	ELSE SET @FULLNAME = @SERVER

	Print 'Migration started from database [' + @SOURCE + '] to database [' + @DBNAME + '] on server  ' + @FULLNAME

	--Cursor to loop throw all tables of Target database (current database)
	DECLARE CurTables CURSOR LOCAL FORWARD_ONLY
	OPTIMISTIC FOR
	SELECT NAME from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by Name

	OPEN CurTables FETCH NEXT FROM CurTables INTO @Table
	WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'Migrating table ' + @Table
		SET @cmd = 'ECHO Exec [Mig_ImportTable] ''' + @Source + ''',''' + @Table + '''  > DbMig.sql'
		EXEC @Result = master..xp_cmdshell @cmd, no_output

		SET @cmd = 'ECHO GO  >> DbMig.sql'
		EXEC @Result =  master..xp_cmdshell @cmd, no_output

		SET @cmd = 'ECHO @ECHO OFF  > DbMig.cmd'
		EXEC @Result =  master..xp_cmdshell @cmd, no_output

		SET @cmd = 'ECHO osql -E -b -S "' + @FULLNAME +'" -d "' + @DBNAME +'" -i "DbMig.sql" >> DbMig.cmd'
		EXEC @Result =  master..xp_cmdshell @cmd, no_output

		SET @cmd = 'ECHO EXIT ERRORLEVEL >> DbMig.cmd'
		EXEC @Result =  master..xp_cmdshell @cmd, no_output

		SET @cmd = 'CMD /c "DbMig.cmd>>DbMig.Log"'
		EXEC @Result = master..xp_cmdshell @cmd, no_output
		IF (@Result <> 0)
			Insert into #Mig_FailedTables (Name) values (@Table)
		FETCH NEXT FROM CurTables INTO @Table

	END
	CLOSE CurTables
	DEALLOCATE CurTables
	Print 'Migration Finished..'
	SELECT Name as [Failed Tables] FROM #Mig_FailedTables
	DROP TABLE #Mig_FailedTables

GO

------------- Migration of data starts from here ------------------

----------------------------------------------------------------------------------------------------------------------------------

USE TargetDatabase						-- Target Database  	** Change This
Exec Mig_ImportDatabase 'SourceDatabase'			-- Source Database  	** Change This - Import Complete database
EXEC Mig_ImportTable 'SourceDatabase','SpecificTableName'	-- SpecificTableName  	** Change This - Import Single Table
----------------------------------------------------------------------------------------------------------------------------------

Compare DB – record counts

Code snippet will loop through all tables of Database SOURCEDB and compare record counts with tables in TARGETDB database

USE SOURCEDB

EXEC SP_MSforeachtable ‘DECLARE @OriCount INT
     DECLARE @Count INT
     DECLARE @Name VARCHAR(400)
     SET @Count = (SELECT COUNT(*) FROM TARGETDB.?)
     SET @OriCount = (SELECT COUNT(*) FROM SOURCEDB.?)
     SET @Name=”?”

IF(@Count <> @OriCount)
BEGIN
         SELECT @Name,@Count as target,@OriCount as source

END

SQL Server Discovery

The objective of this article is to discover the presence of Microsoft SQL Server  across subnet. There are API’s to enumerate SQL Server instances in single subnet (Win32 API : NetServerEnum ) and Microsoft enterprise manger uses this API to populate the list of SQL server available in current subnet. The API NetServerEnum broadcast UDP packets in the network and SQL server respond the message by sending their details. Since UDP packets can’t cross subnets and hence it will only return the partial list in particular domain.

I was working on project where I have to find if on given IP or IP range , any sql server exists or not and if sql server exists I need to find the instance names. etc.

The SQL server discovery module is hosted on web server and will be accessed by Silverlight application via WCF service. The code can be used to determine if SQL server is running or not (Before trying to connect) to build more responsive applications.

The code sends the UDP packet (point to point access) directly to provide ip address on port 1434 and revived the data back from machines.

If server is there the another function connects on TCP channel on TCP IP port to extract Sql server Netlib version.

public class SqlServerInfo
{
    public string ServerName { get; private set; }
    public string IpAddress { get; private set; }
    public string InstanceName { get; private set; }
    public bool IsClustered { get; private set; }
    public string Version { get; private set; }
    public int tcpPort { get; private set; }
    public string NamedPipe { get; private set; }
    public string Rpc { get; private set; }
    public bool IsActive { get; private set; }

    static public List<SqlServerInfo> DiscoverSQLServer(string[] possibleIPs, bool requiredDeepCheck)
    {
        List<SqlServerInfo> servers = new List<SqlServerInfo>();
        foreach (string ip in possibleIPs)
        {
            servers.AddRange(SqlServerInfo.DiscoverSQLServer(ip));
        }
        return servers;
    }

    static public List<SqlServerInfo> DiscoverSQLServer(string ip)
    {
        Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp);
        socket.EnableBroadcast = true;
        socket.ReceiveTimeout = 1000;
        List<SqlServerInfo> servers = new List<SqlServerInfo>();
        try
        {
            byte[] msg = new byte[] { 3 };
            IPEndPoint ep = new IPEndPoint(IPAddress.Parse(ip), 1434);
            socket.SendTo(msg, ep);
            int cnt = 0;
            byte[] bytBuffer = new byte[64000];
            do
            {
                cnt = socket.Receive(bytBuffer);
                string s = System.Text.ASCIIEncoding.ASCII.GetString(bytBuffer, 3, BitConverter.ToInt16(bytBuffer, 1));
                string[] parts = s.Split(new string[] { ";;" }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string s1 in parts)
                {
                    SqlServerInfo sInfo = new SqlServerInfo(s1);
                    sInfo.IpAddress = ip;
                    if (sInfo.CheckIsActive())
                    {
                        servers.Add(sInfo);
                    }

                }
                socket.SetSocketOption(SocketOptionLevel.Socket, SocketOptionName.ReceiveTimeout, 300);
            } while (cnt != 0);
        }
        catch
        {

        }
        finally
        {
            socket.Close();
        }

        return servers;
    }

    private SqlServerInfo()
    {

    }

    public  string SSNetlibVersion(string remoteIP, int port)
    {
        string str = "";
        try
        {
            TcpClient client = new TcpClient();
            client.SendTimeout = 300;
            client.ReceiveTimeout = 300;
            client.Connect(remoteIP, port);
            NetworkStream stream = client.GetStream();
            byte[] buffer = new byte[] {
                0x12, 1, 0, 0x34, 0, 0, 0, 0, 0, 0, 0x15, 0, 6, 1, 0, 0x1b,
                0, 1, 2, 0, 0x1c, 0, 12, 3, 0, 40, 0, 4, 0xff, 8, 0, 1,
                0x55, 0, 0, 0, 0x4d, 0x53, 0x53, 0x51, 0x4c, 0x53, 0x65, 0x72, 0x76, 0x65, 0x72, 0,
                4, 8, 0, 0};
            stream.Write(buffer, 0, buffer.Length);
            byte[] buffer2 = new byte[0xff];
            string str2 = string.Empty;
            int count = stream.Read(buffer2, 0, buffer2.Length);
            str2 = Encoding.ASCII.GetString(buffer2, 0, count);
            string[] strArray = new string[] { buffer2[0x1d].ToString(), ".", buffer2[30].ToString(), ".", ((buffer2[0x1f] * 0x100) + buffer2[0x20]).ToString() };
            str = string.Concat(strArray);
            if (str.Substring(0, 1) == "0")
            {
                str = "";
            }
        }
        catch
        {
        }
        return str;
    }

    private bool CheckIsActive()
    {
        string version= SSNetlibVersion(this.IpAddress, this.tcpPort);
        if (version.Length == 0)
        {
            this.IsActive = false;
            return false;
        }
        else
        {
            this.IsActive = true;
            this.Version = version;
            return true;
        }

    }

    private SqlServerInfo(string info)
    {
        string[] nvs = info.Split(';');
        for (int i = 0; i < nvs.Length; i += 2)
        {
            switch (nvs[i].ToLower())
            {
                case "servername":
                    this.ServerName = nvs[i + 1];
                    break;

                case "instancename":

                    this.InstanceName = nvs[i + 1];
                    break;

                case "isclustered":
                    this.IsClustered = (nvs[i + 1].ToLower() == "yes");   //bool.Parse(nvs[i+1]);
                    break;

                case "version":
                    this.Version = nvs[i + 1];
                    break;

                case "tcp":
                    this.tcpPort = int.Parse(nvs[i + 1]);
                    break;

                case "np":
                    this.NamedPipe = nvs[i + 1];
                    break;

                case "rpc":
                    this.Rpc = nvs[i + 1];
                    break;

            }
        }
    }
}

using the code

List<SqlServerInfo> sqlSrv = null;
sqlSrv = SqlServerInfo.DiscoverSQLServer(address);
//Or
sqlSrv = SqlServerInfo.DiscoverSQLServer(
                    new string[]{ address+ ".01",
                        address+ ".02",
                        address+ ".03",
                        address+ ".04",
                        address+ ".05",
                        address+ ".06",
                        address+ ".07",
                        address+ ".08",
                        address+ ".09",
                        address+ ".10",
                        address+ ".21",
                        address+ ".51",
                        address+ ".100"},true);

Repair Suspect mode

USE Master
GO

EXEC sp_configure ‘allow updates’, 1
RECONFIGURE WITH OVERRIDE
GO

BEGIN TRAN
UPDATE master..sysdatabases SET status = status | 32768 WHERE name = ‘YourDBName’
IF @@ROWCOUNT = 1
 BEGIN COMMIT TRAN
  RAISERROR(‘Emergency Mode Successfully Set’, 0, 1)
 END
ELSE
 BEGIN ROLLBACK
 RAISERROR(‘Setting Emergency Mode Failed’, 16, 1)
END
GO
–Stop mssql service

–Rename the existing LOG file for YourDBName database.

–Start mssql service

DBCC REBUILD_LOG(YourDBName,’C:program files….dataYourDBName_log.ldf’)
GO

DBCC checkdb (YourDBName)
GO

–If DBCC return errors then fix it
— BEGIN
 ALTER DATABASE YourDBName SET SINGLE_USER
 GO
 –Repair the consistency errors if found by above command
 DBCC checkdb (YourDBName,REPAIR_ALLOW_DATA_LOSS)
 GO
 –OR
 DBCC checkdb (YourDBName,REPAIR_FAST)
 GO
— END Fix errors finished

ALTER DATABASE YourDBName SET MULTI_USER

Article applies to SQL Server 2000

Security : SQL Server Passwords

To securely use SQL Server, Microsoft recommends using Windows Integrated Security. In Windows Integrated Security mode, passwords are never stored as your Windows Domain sign-on is used as the security identifier to the database server.
Password and login ID of a registered SQL Server user in Enterprise Manager for SQL Server can be easily retrieved using SQL DMO (Distributed Management Object). Create file SQLPasswords.vbs and open it in Notepad. Add the following code and save it:


Dim pApplication
Set pApplication = CreateObject("SQLDMO.Application")
Call EnumGroups(pApplication.ServerGroups)
Sub EnumGroups(ByVal pGroups)
Dim pServerGroup 
Dim pRegServer 
For Each pServerGroup In pGroups 
 If pServerGroup.RegisteredServers.Count > 0 Then 
  Msgbox "Group :" & pServerGroup.Name 
  For Each pRegServer In pServerGroup.RegisteredServers
   Msgbox "Name:" & pRegServer.Name
   Msgbox "Login:" & pRegServer.Login
   Msgbox "Password:" & pRegServer.Password
  Next
 End If
 If pServerGroup.ServerGroups.Count > 0 Then
  Call EnumGroups(pServerGroup.ServerGroups)
 End If
Next 
End Sub

the script and this will retrieve the passwords of registered servers using your account. If the ‘Always prompt for login name and password’ checkbox is not set when registering a SQL Server, the login ID and password is weakly encrypted and stored in the following registry key: HKEY_CURRENT_USERSOFTWAREMicrosoftMSSQLServerSQLEWRegistered Server X.

How SQL Server stores passwords (SQL accounts) : See article at
http://www.codeproject.com/KB/vbscript/SQLServerPasswords.aspx