Could not set "Ignore Failure" for OLE DB Destiantion component

I am using Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00.

My OLE DB Destiantion component is inserting data into a table. When there is duplicate it will failed. But I want ignore this since I know data is same. But even I set Ignore Failure from OLE DB Destioantion Editor. It will not work and every time you reopen editor, the buttom drop down box always showing as " Fail componet". When I try run it, it will always fail on the inserting duplicated rows.

Does anyone know how can I tell servies ignore this error.

Thanks!

About the table, did you set any unique (or clustered) index on it?

Philippe

|||

Hi Jun,

Setting Ignore Failure doesn't cover all bases. As Philippe alludes to, you cannot violate a Unique constraint.

Hope this helps,
Andy

|||

Yes. I do have unique constraint on that table. But what kind failure it will ignore?

By the way, I get this work around by add aggregate function before. Thanks for the help!

Jun Fan

Could not load file or assembly SQLXMLBULKLOADLib

Greetings,

I'm in the process of migrating several SQL Server 2000 DTS packages to Integration Services packages. One of the old 2000 DTS packages used the SQLXML Bulk Loader component. In order to use the new SQLXML 4 COM object in my Script Task (to initiate the Bulk Loader using .NET code) I've used the tlbimp.exe tool to create a .NET wrapper DLL. I've placed the DLL in the appropriate directory (C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies), successfully added it to my project (with Intellisense working), but when I run the package it fails with the following error:

Could not load file or assembly 'SQLXMLBULKLOADLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Note: I also tried placing the DLL in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 with no prevail.

I've confirm the file exists (and any dependencies) in their appropriate locations. Has anyone else run into this? Any help is much appreciated.

Thanks, Shaun

i'm not sure if this resolves your issue, but have you considered using the ole db destination component to bulk load the data?|||Reason I leaned toward the SQLXML Bulk Loader class is because i have a ton of XML I'm downloading and want to leverage the performance of the XML bulk loader. I'm not aware of how the OLE DB Destination object can help me with this.|||

I've resolved this issue by using... well... what I'm supposed to use. I created a Data Flow task and implemented an XML Source (applying an XSD transformation), Data Conversion (to convert from Unicode) and Ole Db Destination. This successfully loads the data into the database from XML.

|||shaun,

i'm glad that you found a solution to your problem. as you probably already know, the ole db destination is designed to bulk load data.|||

Did you add it to the GAC? You need to add it to the framework folder for design-time only (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727) but at runtime it uses the GAC.

I think the better solution is to re-write to use the XML Source as this is very powerful and handles hierarchical data, but obviously this may not be ideal if you have a lot of packages and XMl formats.

Could not create a connection for the provider invariant name. Verify that this provider is inst

Hi all,

While working on the Integration Services project.

When I try to create a new Data Connection in Connection Managers for ADO .NET SAP Provider, it gives the following exception:

TITLE: Microsoft Visual Studio

Could not set the connection qualifier for the current connection.

ADDITIONAL INFORMATION:

Could not create a connection for the provider invariant name 'Microsoft.Adapters.SAP.SAPProvider'. Verify that this provider is installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=CouldNotCreateConnection&LinkId=20476

Object reference not set to an instance of an object. (Microsoft.Adapters.SAP.SAPProvider)

BUTTONS:

OK

Can anyone describe, what is the resolution for the same.

Have you tried reinstalling the SAP Provider? The error is pretty much saying it cannot find the provider.

Could not create a connection for the provider invariant name. Verify that this provider is

Hi all,

While working on the Integration Services project.

When I try to create a new Data Connection in Connection Managers for ADO .NET SAP Provider, it gives the following exception:

TITLE: Microsoft Visual Studio

Could not set the connection qualifier for the current connection.

ADDITIONAL INFORMATION:

Could not create a connection for the provider invariant name 'Microsoft.Adapters.SAP.SAPProvider'. Verify that this provider is installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=CouldNotCreateConnection&LinkId=20476

Object reference not set to an instance of an object. (Microsoft.Adapters.SAP.SAPProvider)

BUTTONS:

OK

Can anyone describe, what is the resolution for the same.

Have you tried reinstalling the SAP Provider? The error is pretty much saying it cannot find the provider.