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

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

Question on Ole db destination provider - SSIS

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hello there

I have a package that imports data from a file to a SQL Server destination. My file has duplicate rows on the primary key column. This will cause the package to fail, since the table will not allow insertion of duplicate primary keys. I use ole db destination task . To this task, i have set error redirection so that any error that causes the rows that can cause error to be redirected. I am facing a different problem due to this property, i have set my ole db destination -- data access as table or view - fast load. If i run the package, all my rows fail insertion into my table and get redirected to my error output file. However if i change my data access mode -- table or view, this package runs and only the duplicate row gets redirected to the error file.

I am not very clear with this scenario. Can anyone please advise?. Since the input data is very high -- more than 5 million rows. The table or view data access mode will make the data insert very slow.

Please correct me if there is anything wrong,.



View Complete Post

More Related Resource Links

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 2008 - Question on OLEDB Destination to SQL Server 2000

Simple question, In a SSIS 2008 Package is it possible to have a OLE DB Destination pointing to another Server Machine running on SQL Server 2000 to do bulk insert operation ? . what are the potential pitfalls when while inserting into SQL Server 2000 ?

Text Search Strategy Question for the SSIS Gurus...

BACKGROUND: As I have mentioned in some of my other posts I am using SSIS 2005 to replace an existing MS Access 2003 / VBA based ETL engine which I developed some years back.   Part of my existing Access-based ETL performs a text search of the source records and I am now attempting to replicate that functionality in SSIS (replicate in terms of the end-result and not necessarily the methods used to get to that end result).  I have an idea of how I plan to go about this but since am relatively new to SSIS so would greatly appreciate the feedback of those more experienced... DETAILED DESCRIPTION: In the source (Sybase ASE15) database, there is an "object" table (not the actual table name but for illustrative purposes it will suffice).  Within the object table there is a "description" column which is a char(60) datatype.  The description column simply represents a description of the object as defined by the source system end-user. For my ETL solution I allow the ETL administrator to define one or many (1...n) key words or phrases which represent search criteria.  These search criteria are stored in a reference table in my target SQL Server 2005 database (the same database to which my ETL will transform results and store them).  My objective, is as follows:  For each of the 1...n search criteria defined, try and find th

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 question

I have a filename variable and I want to load that variable into a SQL table. Can I use a execute SQL task for doing that? If so any ideas as to how it can be done. Thanks.sqldev

Membership Provider: Remove Secret Question .net 4.0

Im trying to remove the secret question / answer from the create user wizard control. I have removed the controls from the template but in previous versions of .net, you had to edit the web.config requiresecretquestion field to false.. I cant find this in the 4.0 web.config. When i try to add the Membership provider tag, i get an error saying its already added. Where is this being stored now? I cant seem to find it :(

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      

membership provider with custom login form problem / question

Hi all, I am using the .net membership provider, and I can get past the membership.validate user ok and into my secure page.   The problem I have is that when I reach the secured page, I have a login status control which isn't changing from login to logout.   Below is my login code:If Membership.ValidateUser(txtUsername.Text, txtPassword.Text) Then Response.Redirect("/auth/Default.aspx") If chkRememberMe.Checked Then FormsAuthentication.SetAuthCookie(txtUsername.Text, True) Else FormsAuthentication.SetAuthCookie(txtUsername.Text, False) End If Else lblLoginStatus.Text = "Oops! Login not found!" End If    Thanks in advance.

Another dynamic SSIS question


I'm seeking just a simple yes or now as to whether this can be done, and if so - a gentle nudge as to what approach to take.

The business problem is that our organization has used SQL Server as the data warehouse after a pretty intense ETL process that runs overnight. We recently acquired a Netezza appliance to interfact with Siebel (or Oracle BI), and I'm trying to create a process by which there nightly copies of the DW database in SQL Server over to Netezza.  All of this is simple to manually, but the requirements call me for me to create a metadata table of tables that are to be included in nightly and intra-day hourly updates.  This metadata table has the name of each table, the key data field for gettin recent transactions from Fact tables for hourly updates.

Based on input from our Netezza advisor, Netezza can handle up to six syncronous packages running at at a time.  For each scheduled task, the package would look at the metadata table for a list of tables, and use sys.all_columns to build up the package details.  Fortunately, the name of the tables and fields are identitcal from SQL Server to Netezza, and I've created a .Net page that allows developers to specify new tables they've created and the corresponding key date field that creates the Netezza DDL language to create the table and make data type translations.

"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:


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


Newbie question about SQl code and SSIS


Hi Guys,

Im coming from a SAP Datawarehouse background and i don't know much about sql queries or statements. I was wondering if i need this knowledge to learn SSIS, and if so in what situations?

Thanks in advance

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]

Unable to connect with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 with ADO Destination Component


As we are passing through critical phase of the project and we are facing following issues which need to be resolved.Please help on this.

Currently we are transferring Data from SQL Server to DB2 using SSIS (SQL Server 2008).The IBM DB2 version is DB2 ESE 9.1 Fix Pack 5 on Linux 4.8.

1] Unable to connect with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008 at run time (although the connection is tested successfully with IBM DB2 using IBM DB2 .NET Data Provider 9.1.5 in SSIS 2008) and the error is

"[ADO NET Destination [6096]] Error: An exception has occurred during data insertion, the message returned from the provider is: Object reference not set to an instance of an object. "

I tried to use CozyRock DB2 Destination component instead of ADO destinatoin Component then i am able to use IBM DB2 .NET provider at run time.

Also i tested connection successfully using IBM DB2 .NET Data Provider 9.1.5

SSIS Dynamic Connection Provider



I am working on SSIS 2008. My requirement is that I need to connect to SQL Server or Oracle datbase in lookup, but I will get to know this at runtime whether its SQL Server or Oracle. So one option is to create separate connection for each and use separate lookups. But my question is can I use only one connection and one lookup and achieve this? I short can I can change oledb connection provider at runtime?




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