.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

SSIS Custom Destination Component colum mapping issue

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

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.

View Complete Post

More Related Resource Links

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

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!

Custom SSIS task inteface building issue


Hi all,

I'm tying to use the class foundation that SSIS provides to build interfaces like those of built in tasks

I tried to follow this tutorial


I added a Display text property to my task in order to display it when the task is fired, every thing goes well

except three  issues

1. The display value is not persisted( when I change it from the GUI inteface. It is only persisted if it is updated from the BIDS properties grid)

2. The buttons OK, Cancel, Help are not displayed at the buttom of the interface such in the rest of built in tasks, should I add them programmatically and  if it is the case how  do I to persist properties values when user clicks OK.

3. Some cells in the properties grid shows a combo box like those configuring  connection managers. For example one can expand the combo box and selects <New Connection...>  

Or when one property grid cell get the focus then ellipses button appears when he/she clicks then the OpenDialogResult opens up

Or if one selects some properties grid cells then the properties grid changes by

SortPropertiesAttribute for Custom SSIS task issue?



I have decorated internal node class with


SortProperties(new string[] {"Name","DisplayText","Description" })]



SSIS Parameter Mapping Issue


Hi , we are facing a strange issue while executing one our SSIS package. It works on all enviroment excep one. We have checked the software and hardware stack. It is similar to other enviroments. This SSIS is triggered by a ASP.Net application. 

1. The issue comes in Paramter mapping in sql execute task. I need to Map a user variable of type int32 to Numeric datatype. It thows a error "invalid precision value". The same code works on all enviroments except this one. If i change the datatype to Short or Varchar, it works perfectly. Not sure why Numeric fails only on this Enviroment ?

2. But if run the SSIS as a standalone task, instead of trigerring it from ASP.net app, the int32 to numeric mapping explained above works even on this enviroment. So not sure why triggering the ssis it from asp.net fails.  Would there be any diffrence if we run the SSIS through a client or as a standalone.?

I know its a Enviroment issue. But not able to get to the root cause.  any pointers would be really helpful.








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]

Mapping custom values and columns to destination table **Newb question**



I am copying data from one source to destination SQL CE table using the data flow task. I have two extra requirements which I am not sure how to achieve.

1.)  One source column needs to be mapped to two destination columns. In the column mapping dropdown the column name disappears after mapping it to one destination column.

2) some custom values for columns need to be added, which are not present in source. For e.g. a default value for some destination columns which is not present.

Any pointers would be extremely appreciated. Thanks,

Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

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

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..


ssis deployment issue - Unable to load custom dll - due to change in Sql server installation


Hello all

I had a strange issue in testing my package, after it was deployed to a Test server.

The details of the server

Sql 2005 is installed, in the D:\ drive

I have used a package, where i use a custom DLL to read values. Hence, i added that DLL to the GAC. I was in the assumption that this should complete my proceess of deploying this package.


However once i executed the package, i got an error - Could not load the Helper object.

Unable to CAST "system.object"  to namaspace.class.Helper.


I reviewed the setting on my macihine, since as part of development inorder to reference the dll i added this to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies location so that i can reference this from BIDS. I checked that location in my test server, i was not present. This location was present in the D: drive ( i assume this was present since i installed my complete SQL Server in the D:\ ). I copied the dLL in that location and added it to GAC from that location. My package still failed with the same error.


To resolve this issue, i created the same path in the C:\ folder as C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

assemblies was newly created in C: and i added the dll to GAC from there. Now my package worked.


Overriding Existing Custom Destination Component


Hello Guys,

We have the following requirement.

We need to create a custom component, which will be put in our existing ETL Packages. This component will call the Rules engine and will send the data to the destination. I want something which can override the Existing OLEDB Destination component. i.e. a container control which will call Rules engine + the custom destination component.

The idea is that i will need to modify the existing ETL Packages. The only change i need to do is to replace the OLEDB destination component will this Custom Component and no other change should be required.

Can you please suggest, which is the best approach and any articles or code for direction.


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

Custom controls and ViewState management issue



I want to know that when we create custom controls, then is there State Management through ViewState done automatically or we have to handle it explicitely ?Since i have created Custom Controls that is behaving as if it is not maintaining its state on PostBack.

Custom Search issue with freetext


Hi All,

My Environment : MOSS 2007 (sp2) with SQL Server 2005

I have a custom search webpart which uses FullTextSqlQuery to fetch the results from Index. I have a some keywords and a free text boxt to build the search query. Poeple enter their query by selecting the keywords and enter some free text into the text box.

Issue : When any PDF document (Not tested with .Doc) is searched using custom webpart with the numbers like "2,0233.21" I do not get any results returned even though the results are returned using the SharePoint out of box full text search. But when I search the content like "2+0233+21" I get results both in Out of box and in custom search webpart.

Requirement: The Custom search should be able to find the document when numbers like "2,0233.21" are entered. It should work exactly like the out of box search.

Thanks in advance for your suggestions.



SSIS Lookup Transformation Issue

I am having a strange issue, In my data flow i have a Lookup Transformaton which will match on key columns of the fact and is followed by a condetional split that would deside if it should insert (old db destination) or go to update (oledb command) or ignore if no change. I have packages running for the last 1 year with the same logic. But in the recent packages I am experiencing a problem.  example: Key columns for join are - type_no (varchar 16) with all numeric values except one record wihh ' '(space) in it  and type_cd (decimal(18,0)) with values(0,1,2,3,4,5) It worked fine when I test the package. After couple of day running in schedule I get integrity violation and huge file with failed records which are supposed to be blocked at the condetional split as they are already in the fact. When i add a data viewer what i found is for all the llokup columns its having nulls (no match found). Workaround that is working for me for now is - I select full cash and say ok in the lookup transofrmation and again open it and set it back to no cash. Then it starts working as expected. Did anyone come accross this kind of issue? is the some standard that I have to follow to make sure this doesnot happen again  

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

"Mixed mode assembly is built against version 'v2.0.50727'.... " issue and use custom config file

Hi, My Winform applilcation has used Microsoft.SqlServer.Management.Common namespace(assembly :Microsoft.Sqlserver.smo.dll/Microsoft.Sqlserver.ConnectionInfo.dll) and developed in .NET 4.0. Problem:Application is throw an exception when it tries to use above assembly reference methods. Full error text is shown below, "Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information. " Error is resolved when we include "app.config" file with below code, <?xml version="1.0"?> <configuration> <startup useLegacyV2RuntimeActivationPolicy="true"> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/> </startup> </configuration> But, I need to keep configuration file as "renamed.config" and not to include app.config file as of the project requirement.I have set configuration file as "renamed.config" file using below code, //AppDomain.CurrentDomain.SetData("APP_CONFIG_FILE", "d:\\reanmed.config" ); I have also included <Startup> element in this "renamed.config" configuration file and removed the app.config file.But it raise me below error, it always expect app.config file with <Startup> element to resolve this iss

Calculated measures, calculated cells, and custom rollups issue

Hi,  I'm a little bit confused according to the purpose of Calculated measures, calculated cells, and custom rollups as they lead practically to the same goal, is there any differnce between those modes from a technical point of view. And for the last one, the custom roll up the question how to perform this, I need a walkthrough that lead me to undersand the way to set a custom roll up to a given dimesion, I visited microsoft site but the inforamtion that I have is poor, so is there any other source that exposes that as a step by step example? Thank youThe complexity resides in the simplicity
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