.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

SSIS Ado >NET Destination Identity column inserts

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
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      

View Complete Post

More Related Resource Links

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

SSIS Changing Column Order during Transformation

First let me say, I really can't believe this chain of events myself--and they are happening to me. I am upgrading several DTS packages to SSIS on what will be my new production server.  These packages create tables, export them to a flat file, and ftp them off to other locations. What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file.  Then when my pickup/load routines run, the data is out of place and they fail. Can anyone please explain what is happening here with the mapping.  I have evaluated the table and the columns are in the order that I expect.  When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily. I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.  Thanks in advance for any help and/or information you can provide

Fetch Identity column just after inserting a row in table

Hi, Please help me with this question. I have a table and I insert a row into it. How can i select the latest inserted row? Can the 'inserted table' keyword be used outside trigger? ( I mean can we use it in above scenario?? If yes how)   Thanks in advance Tiya

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

Unable to load data into a LookUp column in sharepoint using sharepoint destination

I have loaded details of a customer on a list and was able to load the data with out any problem using sharepoint list destination. We have a lookup column called customerid looking at customer list  . This lookup column is in another list, called Customer Main, so I am trying to load additional data for customer in this customer Main  list which contains a lookup column called customerID. I was not able to load data into this cutsomerid, whereas rest of the data is coming into sharepoint with out any problem. How should I load data into a lookup column. Thanks  

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

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

How to have dynamic column names in an Excel Destination?

I have a situation where both the column names in the source (SQL) and destination (Excel) are being created dynamically. The number of columns, their positions, and datatypes remain the same. How do I change the OLE DB Source and Excel Destination meta-data to handle these changes at run-time?

SSIS 2005 imports column as null

Hi, I am using SSIS 2005 to import excel files to sql server. I have a large excel file with many columns. I have one column -qty that not all rows have data for it-empty. For such rows a another column-value that i need to export is imported as NULL. If column QTY contains a value than column Value is imported fine but if column QTY is blank Value is NULL even if it does have a value. I have played with TypeGuessRows but it doesn't help. Any ideas? Thanks

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

OLEDB/ADO Destionation - Add input column twice to destination columns or static data


(Using BIDS 2008 R2)

OLEDB/ADO Destionation

Reuse input column
Is there any way to map a input column to two destination columns?

State static value instead of mapping column
I have a package for each version of the production system - they insert in the same destination tables - I just need to add a version number to the version column - like '2.1' and '3.0'. The only way I have found so far is to select a fake column in the source like select '2.2' as Version, ..- This is however not very smart when I'm selecting millions of rows.

I would just like to be able to type in a static value instead of mapping a column - any way to do this?

Data Base Properties column Identity value will not accept value change


In the tutorial I am doing I have been requested to change a field in the colums properties of a table.  The ID column is currentl displaying NO and NO again for "is aIdentity.  I can Highlight the values but cannot change to yes.  either in the properties talbe or in the properties window on the right hand side of IDE interface.  How do I change that value?


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


Saving Date column from XML Source to SQL Destination


Hi, I'm trying to load data from an xml file to SQL Server table. I've columns of type DateTime in my xml file. I've used Data Conversion to convert the datetime column to DT_Date and trying to save/update the data using Slowly Changing Dimension, which is throwing an error:

    "[Derived Column [928]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (928)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "DATEVAL" (973)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure."

I've been trying to fix this thing by using all the data types that are related to dates(DBTIMESTAMP,DT_DBDATE, etc) but in vain. Can someone suggest me how to fix this.

Thanks in advance,


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