C-Sharp Script to get IncrementalDate

The below script helps to get maximum date used for incremental load for the SSIS packages:

In below example, we have "TimeStarted" as a incremental date column. You need to create 2 string variables 'IncrementalDate' and 'NewIncrementalDate' in the package.

In script task set 'TimeStarted' (incrementaldate column) as Readonly variable and copy paste the below code and modify the highlighted text (in Yellow) as per the input column name. On executing the package 'NewIncrementalDate' variable will get updated with MaxIncremantal date.

/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
      DateTime NewIncrementalDate;
      String strNewIncrementalDate;

    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */

        strNewIncrementalDate = Variables.IncrementalDate.ToString();
        NewIncrementalDate = DateTime.Parse(strNewIncrementalDate);
        //MessageBox.Show("Before: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
              
    }

    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */

        /*NewMaxEventDate.AddMonths(-1);*/
        //MessageBox.Show("After: " + NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff"));
        Variables.NewIncrementalDate = NewIncrementalDate.ToString("yyyy-MM-dd HH:mm:ss.fff");

    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        try
        {
            if (!Row.TimeStarted_IsNull  && Row.TimeStarted  > NewIncrementalDate)
            {
                //MessageBox.Show("CreateDate: " + Row.createdate.ToString("yyyy-MM-dd hh:mm:ss.fff") + "; NewDate: "
                 //              + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));
                NewIncrementalDate = Row.TimeStarted;
                //MessageBox.Show("NewDate: " + NewIncrementalDate.ToString("yyyy-MM-dd hh:mm:ss.fff"));


            }

            Row.etlcreateddate = Variables.ContainerStartTime;
            Row.etllastmodifieddate = Variables.ContainerStartTime;

        }
        catch (Exception ex)
        {
            throw new Exception("Error in ExtractTestSample script.  Message: " + ex.Message);
        }
    }

}

How to determine largest value comparing two or multiple columns using T-SQL

The below function can be used as a alternate for GREATEST() function in MYSQL:

Create a function with below code in your SQL SERVER database:

Below function compares 11 columns in a table.

CREATE FUNCTION dbo.fnGreatest
   (  @Value0  sql_variant,
      @Value1  sql_variant,
      @Value2  sql_variant,
      @Value3  sql_variant,
      @Value4  sql_variant,
      @Value5  sql_variant,
      @Value6  sql_variant,
      @Value7  sql_variant,
      @Value8  sql_variant,
      @Value9  sql_variant,
      @Value10  sql_variant
    )
   RETURNS sql_variant
AS
   BEGIN
     
      DECLARE @ReturnValue sql_variant

      DECLARE @MaxTable table
         (  RowID      int  IDENTITY,
            MaxColumn sql_variant
         )

        INSERT INTO @MaxTable VALUES ( @Value0 )
        INSERT INTO @MaxTable VALUES ( @Value1 )
        INSERT INTO @MaxTable VALUES ( @Value2 )
        INSERT INTO @MaxTable VALUES ( @Value4 )
        INSERT INTO @MaxTable VALUES ( @Value5 )
        INSERT INTO @MaxTable VALUES ( @Value6 )
        INSERT INTO @MaxTable VALUES ( @Value7 )
        INSERT INTO @MaxTable VALUES ( @Value8 )
        INSERT INTO @MaxTable VALUES ( @Value9 )
        INSERT INTO @MaxTable VALUES ( @Value10 )

       SELECT @ReturnValue = MAX(MaxColumn)
      FROM @MaxTable

      RETURN @ReturnValue

   END
GO








SELECT fnGreatest(date1,date2,date3.....date11)  from <tablename>

DB Connection error while connecting SQL Server Via Excel

I tried to connect SQL Server with excel and faced the below issue:

DBNETLIB ConnectionOpen (Connect()).]SQLServer doesnot exists or access denied.

I was able to overcome this issue by registering SQLVDI.dll through command prompt,

On successful registration a message box prompting registration successful appears.

ERROR: SSAS Cube has no linked measure groups

When we work on deploying SSAS cubes, we may come across the following error:

Errors in the metadata manager. The cube has no linked measure groups. Errors in the metadata manager. 
An error occurred when loading the ..... cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales_sample.0.db\Sales ~.2.cub.xml'.

To resolve this issue perform the below operation:

1. Stop Analysis Service 
2. Navigat to the respective folder where you store Analysis service data "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\".
3. Delete the main folder of the Analysis Service database, e.g., if your analysis service db name is Sales. Then delete the folder named Sales in the path.
4. Also delete Sales.cub.xml file ion the path.
5. Restart the Analysis service.
6.  Deploy the cube.

How to view the value of SSIS variable updated from SQL task

How to view the value of SSIS variable updated from SQL?

In order to view the value passed to SSIS Variable from SQL Task add an script task after the SQL Task and include the code given below:

Consider the variable name is "User::IncrementalDate":


Public Sub Main()
        MsgBox(Dts.Variables("User::IncrementalDate").Value)
        Dts.TaskResult = ScriptResults.Success
End Sub

Note: Check User::IncrementalDate variable as ReadOnly variable i n Script task.

Convert Seconds to HH:MM:SS

DECLARE @SECONDS INT = 5200

SELECT CONVERT(CHAR(8),DATEADD(second,@SECONDS,0),108) 'TOS HHMMSS'

How to Apply Read/Write Mode to a Datbase

TO SET TO READ WRITE

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

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Write*/
ALTER DATABASE [DATABASE_NAM] ESET READ_WRITE WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE DATABASE_NAME SET MULTI_USER

  

TO SET TO READ ONLY

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

USE MASTER
GO
/*Mark it as Singe User*/
ALTER DATABASE [DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

/*Mark the database as Read Only*/
ALTER DATABASE [DATABASE_NAME] SET READ_ONLY WITH ROLLBACK IMMEDIATE

/*Mark it back to Multi User now*/
ALTER DATABASE [DATABASE_NAME] SET MULTI_USER