Work offline with SSIS package

Sometimes it happens that we need to open an SSIS package which contains connections which are not accessible or not allowed to be accessed due to some security reasons. In this post “Work offline with SSIS package”, we are going to learn that how we can open, read, and modify the components of an SSIS package when its connection managers are not able to connect to the underlying data source. This feature can be highly useful when it comes that there is a developer who is not authorized to access the underlying data source being used in the package but he needs to read some column mappings information from the source and destinations being used in the package.

Whenever we open an SSIS package in Visual Studio designer, the designer tries to connect to the data sources being used by the package to verify the metadata. And if it fails … More


Specific row at the top then sort the rest result set in SQL Server

In this post, “Specific row at the top then sort the rest result set in SQL Server“, we are going to learn that how we can order a result set in a customized way which cannot be achieved using ORDER by Clause in a simple way.

To demonstrate this, let’s create a sample table named as “tbl_Department” and insert some dummy rows in it. Below is the code to create the sample table:

IF(OBJECT_ID('dbo.tbl_Department') IS NOT NULL)
DROP TABLE tbl_Department
GO
CREATE TABLE tbl_Department
(
DeptId INT IDENTITY(1,1) NOT NULL,
DeptName VARCHAR(256) NOT NULL,
DeptAddress VARCHAR(512),
DeptContact VARCHAR(15)
)
GO

Now, add some sample rows into the table:

INSERT INTO tbl_Department
(DeptName, DeptAddress, DeptContact)
VALUES
('Engineering', 'New Delhi', '1234567890'),
('Human Resources', 'Chennai', '9876543210'),
('Admin', 'Kolkata', '2763282789'),
('Human Resources', 'New Delhi', '1928272727'),
('Not Assigned', 'Mumbai', '1839288976')
GO

Let’s have a look at the table data now:

Sample table

Sample table

From … More


Use NEWID() inside function in SQL Server

In SQL Server, if we use the NEWID() function inside an user-defined function, it throws an error as “Invalid use of side-effecting or time-dependent operator in ‘newid()’ within a function.

For any reason, if we need to use the NEWID() function inside an user-defined function, we need to do some work around. In this post, we will discuss few tips which can be used to deal with such kind of scenarios.

To demonstrate this problem, first, let’s create a user defined function which uses NEWID() function inside. Though in production environment we might be using more complex logic with the NEWID value, but in this demo, to keep it simple, we will be using the LOWER() function which converts the given value to the lower case.

IF OBJECT_ID('dbo.fn_TestNEWID') IS NOT NULL
DROP FUNCTION dbo.fn_TestNEWID
GO
CREATE FUNCTION dbo.fn_TestNEWID()
RETURNS NVARCHAR(50)
AS
BEGIN
--do some stuff with NEWID and 
More

Extract the first number from an alphanumeric string in sql server 4

In this post, we are going to learn how to extract the first numeric value from an alphanumeric string in SQL Server. To demonstrate this, we will create a dummy table and then we will insert some dummy rows into that table. Finally, we will use a SELECT statement to extract the first numeric value from the given alphanumeric string for each row of the table.

Let’s create the dummy table as below:

--Create dummy table named testTable
IF(OBJECT_ID('dbo.testTable')) IS NOT NULL
DROP TABLE dbo.testTable
GO
CREATE TABLE dbo.testTable
(
val VARCHAR(100)
)
GO

Now, let’s insert some alphanumeric values in this table as below:

--Insert some dummy rows in testTable
INSERT INTO dbo.testTable
(val)
VALUES
('1846855445-DB-FC-D-B-FEDACAE'),
('912898124-B--EB-CBD-EEC'),
('AFAA---E-EDDEC-1080731029'),
('FFDE-DA-B-A-BCE-1807799782'),
('DDFAD-DDE-D-EB-FFDFBB-1683269209'),
('522822568-C-EFC--ACCA-EDB'),
('BDDAC-C-C-A-EC-450054394'),
('DBCD-D-AA-AD-EFA-385921800'),
('EF-B-ED-BBC-FEBC-599561669'),
('BED--F-BC-FD')

We have below sample records in the testTable:

--Get data from testTable
SELECT * FROM dbo.testTable
Sample records in testTable

Sample records in testTable

Now, to extract the … More


Checkpoints with containers in SSIS

In the previous post “Using checkpoints in an SSIS package“, we discussed how to configure an SSIS package in order to enable the checkpoints. Now, In this post “Checkpoints with containers in SSIS“, we are going learn how to configure a package to restart from the first task of the container in which it fails instead of the beginning of the package.

Most of the time, in an SSIS package, containers are used to group a set of tasks in a single unit of work and if the package gets failed, we need to restart the package from the beginning of the container instead of restarting it from the beginning of the package. Let’s create a dummy package and demonstrate it.

Using checkpoints with containers in an SSIS package – Demo

In this demonstration, we are going to use sequence container to group multiple tasks in … More


Using checkpoints in an SSIS package

Checkpoints in SSIS

In this post “Using checkpoints in an SSIS package”, we are going to learn how to configure checkpoints in an SSIS package.

SQL Server integration services use checkpoints in order to avoid the re-execution of an SSIS package from the beginning in case the package fails during execution. Checkpoints can be extremely useful in case the task prior to the point of failure takes a long time to execute. For example, if we are dealing with an SSIS package which is loading a huge amount of data from an OLTP server (highly transactional) to a destination server and is taking a long time to accomplish the data loading operation, and if the package gets failed after the data loading operation (at any other task being executed later on), we would not like to restart the package from the beginning which will take a long time to reload … More


Get error column name in Data Flow Task in SSIS

How to get error column name and error description in Data Flow Task in SSIS

During execution of an SSIS package, when a bad row comes in the data flow task, the task gets failed. However, most of the components (source, transformation, and destination) in the data flow task exposes an error output path which can be configured to redirect these invalid records to the error output path and then we can log these redirected bad records using a destination component. Once all these bad records get logged, we can investigate the root cause of the errors, fix them, and then these bad rows can be reintroduced again.

The error output path in the data flow task contains two four byte int columns, ErrorColumn and ErrorCode, which represent the lineage id of the error column and the error code respectively. The values in both these columns are numeric which … More


Covering index and its usage

What is a covering index

A covering index is an index which satisfies all the columns being used in a query without performing a further lookup either to the clustered index or to the base table. It contains all the columns whether they are used in SELECT, JOIN, WHERE, GROUP BY, or ORDER BY clause of the query. Covering index works like a pseudo-clustered index for the query. It improves the performance of the query by adding non-key columns in the index which help the query optimizer to remove any lookup which can be a key lookup or a RID lookup. A key lookup is performed in case the base table accommodates a clustered index otherwise a RID lookup is used by the query optimizer to fetch the non-key columns needed by the query. To know more about key lookup and RID lookup, click here.

The optimizer doesn’t need … More


Unfolding | Set Based Approach | Get Continuous Start Date of the Current Project – Episode 8 1

In this video, we are going to learn;

  1. How and when to use Apply operator in SQL Server, and
  2. How to compare rows on a given logic

You can download the script used in this example from below the video.


/******************************************************************************************************************************************************
Start using the SetBasedApproach database
******************************************************************************************************************************************************/
--Start using the existing database SetBasedApproach which has been already created in this series
USE SetBasedApproach
GO

/******************************************************************************************************************************************************
Create required objects
******************************************************************************************************************************************************/
--Drop the EmployeeMaster table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeMaster') IS NOT NULL)
DROP TABLE dbo.EmployeeMaster

--Create Employee master table
CREATE TABLE dbo.EmployeeMaster
(
EmpID INT NOT NULL PRIMARY KEY,
EmpName NVARCHAR(255) NOT NULL
)

--Drop the project master table if it exists already in the database
IF(OBJECT_ID('dbo.ProjectMaster') IS NOT NULL)
DROP TABLE dbo.ProjectMaster

--Create project master table
CREATE TABLE dbo.ProjectMaster
(
ProjectID INT NOT NULL PRIMARY KEY,
ProjectName VARCHAR(255)
)

--Drop the EmployeeProjectDetail table if it exists already 
More

Dynamic SQL in OLEDB source component in SSIS 1

The metadata could not be determined because statement contains dynamic SQL

When we use a batch of T-SQL statements which contains dynamic SQL or uses temporary tables inside, in an OLEDB source component placed in a Data Flow Task of a SSIS package, we get an error during column names extraction because the source component gets failed to fetch the metadata (columns and their data types) for the given T-SQL batch.

In this post, we are going to learn how we can use a dynamic SQL statement or a batch of T-SQL statement which does not expose its metadata directly, in a source component which could be an OLEDB source, or an ODBC source being used to fetch data from the SQL Server.

Follow below steps to generate the error:

  • Create an integration services project in visual studio.
  • Add a Data Flow Task in the package.
  • Create an OLEDB connection
More