.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

ODBC Destination in SSIS

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server


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!


View Complete Post

More Related Resource Links

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

extracting from multiple tables in an odbc call/ssis

Hi,   I have an odbc for DBISAM, and can use it to import into sql server, but only one table at a time.  (when it get's to the choose table screen, it will only let me create a single SQL query.   does anyone know if I can extract from multiple tables using this?   If not - what would I need to change in the DTSX file so that my query goes from "select * from tablea" to "select * from tableb" and the destination table is also "tableb"?   also - can the DTSX file be change to run for multiple tables?

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

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]

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


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.

row count for source and destination in ssis



I need an efficient way of counting number of rows coming from source and also going to destination after passing through all the transformations.I know row count transformation is there but as it aggregates and makes the flow very slow with heavey volume, i dont want to use it.

Pls suggest me some good way of counting rows, source can be flat file/excel/access/sql server



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 : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

SSIS -2008 R2 - ODBC Connection error - Password does not save


Please know that I am new to SSIS 2008 and XML. Have a package I am trying to deploy. Am on a HV 64 Bit environment SQL Server 2K8 R2. Have an ODBC connection to our HBOC system as a data source. As always package runs in test environment and not in prod.  I need a bit of insight into the what and why's of the configuation file and how to change out the XML. 

Main issue is the password not saving and how to handle the ODBC side of things. I appreciate any help in this matter as I have searched with no real understanding of XML and best practices of creating the Congif file.


SSIS Object model - dynamically identifying the source and destination tables of the data flow/ tran


Hello All,

I would like to identifying the source & destination tables of the data flow or the transformation task.

I need to create a custom component which i can put into existing packages which can do some validation.

Please suggest ways i can accomplish this.




SSIS package with Webservice as destination


I have a table is SQL server database A that is my source.

I have another database B which is accessed via webservice call.(its a CRM server basically).

My intention is to transfer data from A to B while B is accessible only via web service. I need to update existing one and create the missing one.

Currently I am using script component, and on every insertion of a row, i call the webservice to check if the record exist or not. If it exist I update it else create it using webservice call itself.

All this happen in Input0_ProcessInputRow(Input0Buffer Row) function.

Now this method is making 2n webserive call which is making the performance very slow.


I want to optimize the approach. Is there a way where I can retrieve whole set of rows in source table in preexecute(), filter it and store it in a List. This way, i just need to check the list a perform update ro create accordingly preventing my webservice call.

Any suggestion to optimize this or even some better approach?

Its actually a CRM server and I am trying to update and create contacts in CRM sync with a database.

SSIS Flat File Destination


I have a requirement to output some large data sets as flat files.

They have specified the file format and I have no issues with all but one thing. They want the data files to end with "the standard EOF character that is used in Linux". When I look at the file I produce with SSIS in a text editor that shows the HEX values the last thing I see in the file is the newlline (0A) chactare for the last line. How can I get it to insert this EOF character they want?

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

Update column value in SSIS before inserting into a destination


Hi all,

I have an SSIS package in which I have various ole db data sources and some logic. Anyhow I am now at the stage where I have 2 data sets (not sure if it's the correct term in SSIS terminology). The first data set comes out straight from an ole db source (just an SQL SELECT) and the second one comes from 2 other sources which have been joined and manipulated.

Now what I want to do is to update some of the values in the first data set with values coming from the second dataset (they have a common key for that) and insert the result in a SQL Server table (using an ole db destination). In T-SQL, the update part would be quite straighforward:

SET Col1 = TableB.Col1,
 Col2 = TableB.Col2
ON TableA.Key = TableB.Key

For inserting the result of the update with SSIS in the destination table, it is straightforward in my case as it's an empty table. I believe I can thus use the ole db destination table or view - fast load.

My questions being: What is the best approach to do that UPDATE part in SSIS? That's the part I can't figure out.

Kind regards,

Francois Malgreve


Kind regards,

Francois Malgreve

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