DtsComException

DFLD: How to capture/refresh Dataflow column metadata from multiple packages using Dataflow Discoverer?

Dataflow Discoverer (DFLD) is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting. For information on how to install and use DFLD utility,visit http://dfld.codeplex.com

DFLD works on one package at a time. It’s a typical ops requirement to keep the DFLMetadata of all SSIS packages up to date. This can be automated by creating a SSIS package similar to the one shown below Continue Reading

SSIS: How to get name of ErrorColumn from Error Output?

SSIS automatically creates two columns on the Error Output from dataflow components – ErrorColumn and ErrorCode. ErrorColumn contains the column ID of the erroneous column. Column ID is a number that is unique to a dataflow within the package. Similarly ErrorCode contains the error number that caused the row to be rejected. At the moment, SSIS by default does not provide ErrorColumn name and ErrorCode description. These can be derived using the methods described below. Continue Reading

SSIS Dataflow Discoverer (DFLD)

What is Dataflow Discoverer?

Dataflow Discoverer (DFLD) is a command-line discovery utility that detects and writes the metadata of SSIS dataflow columns to a SQL Server lookup table. DFLD detects Dataflows nested within containers up to any level of nesting.

Why use Dataflow Discoverer?

SSIS dataflow column metadata generated by this utility could be used to enhance the error reporting capability of your SSIS package. For example to get the name of ErrorColumn from the Error Outputs which is not available by default in SSIS yet.

What are the minimum system requirements to use Dataflow Discoverer?

  1. .NET framework 4 or above
  2. SQL Server Database (any version)

How to install Dataflow Discoverer?

Verify that your system meets minimum requirements, then

  1. Execute the script on your SQL Server. By default the sql script will create a SQL Server database – ETLMetadata and a lookup table – DFLMetadata. You could also create the table DFLMetadata in any other SQL Server database, in that case just comment out the database creation segment of the sql script.

  2. Copy the executable to a local folder. It is recommended to add the executable path to System Path environment variable.

Continue Reading