Filtered Index in SQL Server

Is it possible to create a unique constraint/unique index on a column which already has duplicate values?

Answer is "YES" with the help of filtered index.

In general, if we create a index every single data on a column will be moved to index pages whereas in the case of filtered index(since we are using where clause to filter the data) only the data which matches the filtered condition will be moved to index pages.

create unique index IX_UNIQUE_TABLE_TEST_COL1 on TABLE_TEST(COL1) 
where id > 1000;

The above syntax will create unique index only on the ID's which is greater than 1000(assume that we have duplicate values till ID = 1000).

In case if we are already aware of duplicate values then we can do something like below

create unique index IX_UNIQUE_TABLE_TEST_COL1 on TABLE_TEST(COL1) 
where ID not in (<list of duplicate values>)

SQL Service Restarts Time/TempDB Creation Time

As we all know tempdb will be recreated everytime when the SQL Server service restarts.

How do we know the timestamp of SQL Server service restarts/tempdb creation through query?

1) Using Dynamic Management View: (Not everyone will have this permission)

SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO

2) Whoever with Read Access can use the below query.

SELECT CREATE_DATE AS LAST_RESTART_TIME
FROM SYS.DATABASES
WHERE  NAME = 'tempdb'

(or)

SELECT CRDATE AS LAST_RESTART_TIME
FROM MASTER.DBO.SYSDATABASES
WHERE NAME = 'tempdb'

Retrieve Error Message in SSIS

Is there any way to get a string variable of error message shown in Execution result tab of a package.

Assume you encountered an error in your SSIS package, now all we need to log that error into your own table (other than SSIS catalog DB's), you can create a Execute SQL task with the INSERT script parameterized. Paramerter you need to use in Parameter Mapping tab isSystem::ErrorDescription.

INSERT INTO LOG_TABLE(ID,ERROR,ERROR_TIMESTAMP) VALUES(1,?,GETDATE());

Note: You can utilize other System Variables like PackageName,MachineName etc to log into the table.

Please refer screenshot below:

MySQL Stored Procedure and Error Handling Construct

/*Changing Default Delimiter from ; to $$ for creating stored procedures.*/

DELIMITER $$ 
DROP procedure IF EXISTS 'SP_PD_CREATE_EMPLOYEE'$$

CREATE PROCEDURE SP_PD_CREATE_EMPLOYEE()
BEGIN
-- Variable Declaration goes here.

DECLARE V_EMP_CITY VARCHAR(20);
DECLARE V_EMP_ID BIGINT;  

/*
(Note: Variables should be declared before handlers and assigning values to variables should come only after handler declarations);
*/

DECLARE exit handler for sqlexception
  BEGIN
    -- ERROR
  ROLLBACK;
END;
  
DECLARE exit handler for sqlwarning
 BEGIN
    -- WARNING
 ROLLBACK;
END;

START TRANSACTION;

SET V_EMP_CITY  = 'CHENNAI';

  INSERT INTO table_name (id, name, address) values ('1','Avinash','xpertdeveloper.com');
  UPDATE second_table set name="xyz" where id=4;
COMMIT;

COMMIT;
END $$

DELIMITER ; -- Switch to Default Delimiter ; from $$

-- Error Handling - Rollback,Capturing Error messages and log in table.

DECLARE EXIT HANDLER FOR SQLEXCEPTION -- ERROR HANDLING STARTS HERE (CATCH BLOCK)
  
BEGIN

ROLLBACK;
    
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT, ERRNO = MYSQL_ERRNO;
    
SET FORMATTED_ERR_MSG = CONCAT('[SQLSTATE : ',code,'] [MYSQL_ERRNO : ',ERRNO ,'] MESSAGE : ',msg);

 /*Log Fail Status; */
    
INSERT INTO LOG(ERROR_MSG) VALUES(FORMATTED_ERR_MSG) VALUES(FORMATTED_ERR_MSG);

END;

Move Multiple Files from Source to Destination using Execute Process Task

When multiple files to be moved from source to destination we might have used ForEachLoop Container with the File System Task in it.(ForEachLoop Container iterates and move one file at a time to destination location using File System Task).

There is one other way(using Execute Process Task) through which we can move all files at once from source to destination without iteration logic.

Execute Process Task is the task in SSIS where in which we can write windows dos commands(as we write in cmd prompt in windows).

We do write dos commands as follows for moving files from one location to other as below:

move "F:\Entertainment\*.*"  "D:\MyFolder\Entertainment\"

*.* represent file name of any names and file type of any type.
We can add filters by placing wildcard character as per our needs.
For example: 
Move only movies starts with character "S" of type mkv from
F:\Entertainment to D:\MyFolder\Entertainment can be achieved as below

move "F:\Entertainment\S*.mkv"  "D:\MyFolder\Entertainment\"

Similar way in SSIS, we need to write code dos commands in Execute Process Task.
Below is the screenshot of the Execute Process Task for reference.

Firstly Switch to Process Tab and in the Executable Field paste the below command

C:\Windows\System32\cmd.exe

Second step is to write the dos commands as below for our requirement(i.e. move files in our case) in the Arguments Field.

/c move /y "F:\Entertainment\S*.mkv"  "D:\MyFolder\Entertainment\"

/c – required when running windows commands from the Execute Process Task

/y – Disable windows prompts such as  "Do you want to overwrite an existing destination file?".

Yes we all set to execute the task to move multiple files from one place to other using Execute Process Task.

Additional Comments:

If you want to write the same as configurable through expressions use below construct:

Create two SSIS variables for storing SRC and DEST location as follows:

@[User::V_FILE_SRC_LOCATION] = F:\Entertainment
@[User::V_FILE_DEST_LOCATION] = D:\MyFolder\Entertainment

Switch to Expressions tab and paste the below in Arguments field:

"C:\\Windows\\System32\\cmd.exe /c move /y \"" +  @[User::V_FILE_SRC_LOCATION]  + "\\S*.mkv\"" + " \""+ @[User::V_FILE_DEST_LOCATION] + "\\\""

SSIS Run64BitRunTime

I created one simple package which transforms data(Table Data) from Execute SQL Task to Flat File Destination.

While creating connection manager for OLEDB I ensured i could reach server by checking "Test Connection", it connected successfully.

Now when i try to execute, i came across this below error:

Source: Start Execute SQL Task 
   Description: Failed to acquire connection "<Connection Name>". Connection may not be configured correctly or you may not have the right permissions on this connection.

Solution which i found was to change the Debug options Run64BitRunTime to False.(By default, it will be set to TRUE).
How to reach the below screen:
Right Click Package name->Properties->Debugging(Under Configuration Properties)
Please refer the screenshot below:

Error generated has no relation to the solution,but still documenting it here for searching solution in future.

Read and Write Object Variable in SSIS

Please consider the below scenario:

Aim is to load the excel sheet data into RDBMS table only through SSIS.
(i.e. There is a limitation to not create any tables for separating comma separated values).

1) Create two variables(User::V_SOURCE_DATA,User::V_DEST_DATA) of object type in SSIS.

2) I directed all the rows read from excel sheet into a Record Set Destination.
So now i captured all my source data in the object named User::V_SOURCE_DATA.

3) Now i am going to create a simple C# script which reads data from object(User::V_SOURCE_DATA) and separates HOBBIES column using ","
and populates data into object(User::V_DEST_DATA).

using System.Data.OleDb;

// Create data table to hold data of output format with two columns(EMP_ID,HOBBIES).

DataTable dest= new DataTable();
dest.Clear();
dest.Columns.Add("EMP_ID");
dest.Columns.Add("HOBBIES");

// Read Source Data

DataTable dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::V_SOURCE_DATA"].Value);

string emp_id = "";
string hobbies_str = "";

foreach (DataRow row in dt.Rows)
{
emp_id = row[0].ToString();
hobbies_str = row[1].ToString();

// MessageBox.Show("EMP_ID = " + emp_id + "; HOBBIES = " + hobbies_str);

string[] hobbies_str_split = hobbies_str.Split(',');

foreach (string item in hobbies_str_split)
{
// MessageBox.Show("EMP_ID = " + emp_id + "; HOBBIES_SPLIT_STR = " + item);
  // Fill new DataTable with Fund_ID
DataRow _fill_data = dest.NewRow();
_fill_data["EMP_ID"] = emp_id;
_fill_data["HOBBIES"] = item;
dest.Rows.Add(_fill_data);
  
}
   
}

Dts.Variables["User::V_DEST_DATA"].Value = dest;

4) Now the output data available in the object named User::V_DEST_DATA.

5) Atlast data from object read using FOR_EACH_LOOP container and passed into destination using EXECUTE SQL TASK Insert Script.

Note: When reading from object, map the output column values to object type variable else datatype mismatch problem will arise.

INSERT INTO EMP_HOBBY(EMP_ID,HOBBY) VALUES (?,?);