.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Bug when using SSIS OLE DB Destination Component using the IBM DB2 v9.# driver

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server
I thought I woud pass on some helpful information with a problem we been experiencing writing to DB2.

It seems that the SSIS OLE DB destination component experiences problems using the IBM DB2 Driver during validation when writing to large DB2 tables ~ 4 million rows. Usually results in a "out of memory" error. We have open a case with Microsoft.  

Until this can be fixed there are several alternatives 
1. If you want to use the OLD DB Destination Component create a view on the Table and add a Where 1=2 so no records return 

2. Use the OLE DB Command component  .. Insert into table (col1,col2) values (?,?)  - this is actually 2 x faster

3. CozyRoc has a excellent DB2 Destination custom component that uses IBM Db2 driver bulk copy API it's 20x faster - very impressive! http://www.cozyroc.com/ssis/db2-destination

hope this helps..


View Complete Post

More Related Resource Links

SSIS Custom Destination Component colum mapping issue


Hi ,

I am facing the below issue.

I am developed a custom destination component.I have custom UI also to set some properties.

Once i save the component and do the mapping in the Show Advance Editor Colum Mapping.This is working fine.

The problem starts Next.If I edit the component and do some changes  and say Ok ,In the output colum mapping all the mappings which i have already been set is getting removed.Then again i have to remap it.Ho to keep the existing mapping if we edit the component?

need help to solve this issue.

SSIS pakage fails "component "OLE DB Destination" (67)" failed validation and returned validation st


I have a package that is executed within a SQL job. The package has been running successfully since quite some time now. Recently I had to rebuild the DB schema (as I was testing the deployment of my application). The package ran successfully once after I re-built the underlying DB  schema.  But it  failed with the following message the next day but has been running successfully automagically after it had failed. 

Note : The schema before and after the rebuild operation has not changed.

The error message thrown on the day it failed is as below (loginname/tablename/Task name are scrubbed) :

Executed as user: <User Name>. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  7:00:00 AM  Error: 2010-11-13 07:00:10.03     Code: 0xC0202009     Source: <Task Name> OLE DB Destination [67]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Connection is busy with

Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

Accessing ActiveX Component functionality from SSIS

Hi I have my SSIS Package, from which I need to access the functionality of an external application.  The external application have an API exposed in form of ActiveX Component.  I have to access that ActiveX components. Now, how to have that ActiveX components exposed to my SSIS package? What are the steps to access that ActiveX component ? Request you to reply at the earliest as this is a critical requirement.  Please note that I am very new to SSIS technology.  

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay

SSIS script component bugs at around 10k rows

I'm trying to load a dimension table from a tab delimited file to a parent-child table. I have made a script like this: public override void Input0_ProcessInputRow(Input0Buffer Row) { String level0 = null, level1 = null, level2 = null, level3 = null, level4 = null, level5 = null; while (!Row.EndOfRowset()) { if (Row.Description_IsNull || Row.Description == "") { if (!Row.NextRow()) break; continue; } if (!Row.Level0_IsNull) { Row.ID = Row.Level0; Row.ParentID = null; level0 = Row.Level0; Row.Description = Row.Description; } else if (!Row.Level1_IsNull) { Row.ID = Row.Level1; Row.ParentID = level0; level1 = Row.Level1; Row.Description = Row.Description; } else if (!Row.Level2_IsNull) { Row.ID = Row.Level2; Row.ParentID = level1; level2 = Row.Level2; Row.Description = Row.Description; } else if (!Row.Level3_IsNull) { Row.ID = Row.Level3; Row.ParentID = level2; level3 = Row.Level3; Row.Description = Row.Description; } else if (!Row.Level4_IsNull) { Row.ID = Row.Level4; Row.ParentID = level3; level4 = Row.Level4; Row.Description = Row.Description; } else if (!Row.Level5_IsNull) { Row.ID = Row.Level5; Row.ParentID = level4; level5

SQL Server 2008 Script Componant Error - [SSIS.Pipeline] Error: component "Script Comp Name" (48) fa

Hi All,   I am facing one strange issue in SSIS 2008. I have developed one SSIS package which is importing excel data into SQL Server 2008. This Package contains some Scirpt componants. This package is working fine on Windows XP machine but when I am trying to run on Windows 2003 server it gives me error "[SSIS.Pipeline] Error: component "SCR RDSTableRelation" (48) failed the post-execute phase and returned error code 0x80004002". Surprising thing is that if on windows server 2003server  if I drag a new script componat and paste the previous script code itself then it works fine. Even if I copy-paste the existing script componant and give source-destination connectino to this new script componant then also it works.

SSIS 2005 - XML Source no data loading to sql server database destination

Hello I am trying to load data from xml source to sql server destination, but the task executes with no data loaded to destination. i really appreciate any guidance in working and troubleshooting xml sources. Regards Harris

Anyone can share the code (or) the component - XML Destination Adapter

Is there anyone, who can share me the Code (or) the component itself for XML Destination Adapter.  I have a requirement to create XML file as a destination from the source being 3 to 4 tables.  Request your quick response for this.  Thanks a lot.

SSIS Ado >NET Destination Identity column inserts

Hi I am trying to build an SSIS package that imports the identity values from one SQL table to another.  I have an ADO .NET destination connection as the import is for SQL Azure although Im currently testing it in SQL 2008.  The connection has RetainSameConnection set to True.  Before the import I have a SQL task that runs SET IDENTITY_INSERT  activityDisplayGroup ON.  This executes but then I get an error message at the data flow task.  [Destination - activityDisplayGroup 1 [1]] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'activityDisplayGroup' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column. In the profiler I see insert bulk "dbo"."activityDisplayGroup" ([name] VarChar(200) COLLATE SQL_Latin1_General_CP1_CI_AS) Is it at all possible to insert identies using the ADO .NET, if so what have I missed?   Thanks Dan      

"Version90 database compatibility level is not supported." After SSIS Destination Upgraded from 2005


We have a moderately complex SSIS package that uses a script task to dynamically create a Transfer SQL Server Object task, which then moves data and schema of a varying definition from source to destination.  The SSIS package was written in BIDS 2005.  The source system is still SQL 2005 (with latest updates) and the destination system is now 2008r2 (with latest updates).  When the package run, we get the following error when we try to execute the dynamically created transfer task: Description: Execution failed with the following error: "Version90 database compatibility level is not supported.".  sp_dbcmptlevel for the source and destination databases returns "The current compatibility level is 90.".  Master on the target system is predictably at level 100.

This leads to several questions:


Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

ODBC Destination in SSIS



I have a problem that stops me from using Integration Services as THE ETL tool.

My goal is to load a data warehouse type of a database. The database is MaxDB (former SAP DB), but this is not the point. Let's take ANY ODBC compliant DB and assume all I have is ODBC driver - no OLE DB driver.

I figured out how to read from ODBC source (using Data Reader and ADO.NET provider for ODBC).

Now my question is how do I output/write my data into ODBC source? When I try to use OLE DB Destination it does not give me an option to use .Net Provide for ODBC. I tried other "destinations" with no luck.

I use this version of SQL Server 2005: (Microsoft SQL Server 2005 - 9.00.1187.07 (Intel X86)   May 24 2005 18:22:46   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Please, help!


ssis how should i load data from excel to Xml destination



could any one tell me how could i load data from EXCEl file to XML
if some one could provide with appropriate link then it will b  a great help

i have  taken Excel source part is completed ..but what to do after that..


How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend