Category : SSIS

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

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

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