Flat File Parsing Problem - 289446

simonsabin — Thu, 09 Mar 2006 18:56:20 GMT

The latest version of this package is available at http://sqlblogcasts.com/files/4/integration_services/entry412.aspx

In response to fourm post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=289446&SiteID=1

This shows handling a ragged flat file input where the number of columns may be not guaranteed. Now include redirecting errors to an error output

Enhanced Error Component

simonsabin — Wed, 08 Feb 2006 13:20:42 GMT

This custom component is used to to add the column name an error occurred in to the data flow in Intergration Services.

It requires the use of SQL Server Service Pack 1. If you get the error "Could not load type 'Microsoft.SqlServer.Dts.Design.IDtsPipelineEnvironmentService' from assembly 'Microsoft.SqlServer.Dts.Design, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'" it is because you do not service pack 1 installed

Following a false start this component now uses the pipeline service to obtain the column details from the upstream component. This requires the user edits the component, which launches the user interface. In the UI you can select whether you want the column name and or the error description added to the flow

Installing the component,

Un Install any previous versions
Run the setup
Open VS and load or create a SSIS package
Open the toolbox, right click and select "Choose items..."
Select the SSIS Data Flow components tab
Check the check box next to the enhanced error component

This should now result in the Enhanced Error Component appearing in the toolbox.

To use the component it is simple,

Open your SSIS package
Drag the Enhanced error component onto the data flow.
Attach an error output to it
Double click on the component
Select whether you want to have the error column and/or the error description added to the flow.
Connect the output of the component to a destination or another component
Run your package.

This has not had extensive testing with destination components. It is best for use when errors are raised against a single column.

I am currently trying to resolve what the error codes from the OLE DB Destination are.

Feel free to contact me using the links above witany feedback/bugs/suggestions.

I hope you find it usefull

Transforms

Flat File Parsing Problem - 289446

Enhanced Error Component