.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 SQL SP and Update statements

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
I have a SP that does a bunch of selects and some updates, it ends with doing a select from several temp tables its built, and then doing the update.  My questions is, when I run it manually for the first time i get all the records i would expect.  If i run it from SSIS for the first time i dont get the results i expect, its like the updates that are in the SP have already occurred and so it reduces the number of records I would expect to get back.  My question related to the validation peice, does it actually run the SP when it validates, if it does it would answer my question i guess since its doing the updates.  If it doesnt, not sure hot to explain the diff

View Complete Post

More Related Resource Links

Multiple update statements per row in Gridview Edit


I want to set up a gridview that will display data like this

WeekEndingDate Project Category Sun Mon Tues Wed Thu Fri Sat
8/14/2010      proj1   test     2   1   2    3   2   1   0
8/21/2010      proj1   test     0   2   2    2   2   1   0
8/28/2010      proj1   test     0   1  

configure both select and Update statements in ADO.NET

 i would like to retrieve a value with select and perform a calculation and then save using update in the same batch code?  

update table with SSIS

  Hi, sorru for my english   I have two tables: intervenant: idInt (primary key) name login (foreign key) (accepting the null) compte: login (primary key) idInt (foreign key) I'd like to know how to do an UPDATE in the table "intervenant" once I filled the login field of the "compte" table.     thanks

Multiple sql server update statments in SSIS OLE DB Command


Is it possible to issue multiple sql server update statements in SSIS OLE DB Command?


I Tried the following with no luck


update table1 set column1 = 1


update table2 set column1 = 1

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 - Data Flow Task Reads Fast But Importing the Output,Update & Deleting Extremely Slow & runs lo


Good day, I need help please.

In my source I read about 4mil records in a matter of seconds But Importing the Output to the Same Table takes about an 1 hour to update.

My Source Read is the Same Table to which i'm Importing, Updateing,Deleting

Importing: Table Lock & Check Constraints are checked

Please any ideas & suggestions will be great.

Thank You

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

.NET Framework update breaks scripts in SSIS


During SQL Server SP2 timeframe a pending security update from the .NET Framework team was announced. That fix, when released would break any SSIS package using scripts. In response to this SQL Server Integration Services provided a fix into SP2 (as well as corresponding fixes for RTM & SP1) that would mitigate this issue, including log messages that would point to the KB below. The .NET Framework fix was released on Tuesday 7/10/2007 and thus we expect to see it surfacing on SQL Server Integration Services applications using scripts (both in data flow and control flow), most likely on machines running 'pure' RTM and/or 64bit installations.

The related KB article describing the impact to SSIS packages is available here:


Here are the details of the .NET Framework 2.0 fixes:

http://support.microsoft.com/kb/928365 (for Windows XP/2003/2000)
http://support.microsoft.com/kb/929916 (for Windows Vista)

What is the meaning of UPDATE and DELETE WITH(NOLOCK) statements?


[1] tells: 

  • "READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement" 

How is it possible to ignore something which cannot be specified? 
Why to continue having it? 
Why were they permitted/introduced for DELETE and UPDATE? 

What did UPDATE (DELETE) WITH(NOLOCK) mean in earlier versions of SQL Server when were they introduced?

[1] Table Hints (Transact-SQL)
SQL Server 2008 R2

I did not expect that the only question which was rhetorical would be answered.
Let's consider I did not ask it

How to update Sharepoint List associated to a workflow with SSIS ?


Hi all,

I've started a new SSIS project which needs to sync daily a SharePoint list into a SQL 2008 database.

Every day the SP list is updated with new suppliers. It is associated to a validation workflow in order to get a clean list.

My SSIS project is looking good so far, when I run it I'm able to get approved records from the SP list into my SQL table.

But I need to go further. I need to tell to the SP list that the datatset I have at the end of my SSIS flow has been synced.

I need to do that in order to have these records only once otherwise at the next run I'll get duplicates.

So my idea was to go for a boolean field in the SP list and update this field and the end of my data flow.

I'm deriving the colum in order to update that in my flow. I set all the records to IsSync == True

But when I run the flow and I check the SP List...only the version field in the SP list has changed and nothing else!

Any help would be appreciated !



Update a Sharepoint List in SSIS (Web Service)?


Good day,

I'm trying to update a Sharepoint List withh SSIS (Web Service)?
I tried using Web Services with vbscript/javascript but struggled for days.

I'm working on a Remote sever diff to the Sharepoint & SQL server (3 diff serv)

But thought if i could get a package to update the list (Sharepoint document library) and then execute the package using vbscipt/javascript through a command prompt.

Firstly i would like to get the package to work before i try & execute the package remotely.

Where can i also find the ssis sharepoint list adapters installer package to install.

Please Assist!

ASP.Net Gridview Edit Update Cancel Commands

In ASP.Net 2.0, GridView Control also provides the functionality to edit and update the data retrieved from the database using CommandField template. You can cancel the action using Cancel Command of the CommandField. GridView consists of events that can be used to perform the actions like edit, update and cancel upon the Data items displayed in the ASP.Net GridView Data Control.

How to format and update GridView and DataGrid rows using JQuery

The behavior described in this question is as expected. When you set text of a cell in grid, it directly affects HTML that is going to be rendered. When you set text value of a cell, it means that you are setting innerText of the cell. The column that GridView creates for command fields (Edit, Delete and Select) are a (anchor) or button elements. So you can see what will happen if you set text value in that cell. It will wipe out those link or button controls and replace them with simple text string.

Update Vs SystemUpdate

Many of you might noticed that share point ListItem has Update() method as well as SystemUpdate().

What is the difference between these two methods and why MOSS has two different APIs for updating an ListItem

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

>>>How to update multiple rows of gidview by one click



I want to add multiple rows of a gridview if the data for multiple fields are same.

Idea for this is that a user may search a record with some parameter e.g. Sector, plot.

For example 15 records being displayed in gridview.

There should be checkbox before every row and if user wants to update Plot and Flat No that is same for 10 rows out of 15 so he will check the rows and update.

For this a dialog box should appear asking for the parameter i.e. Plot and Flat or something else next time may be it will be City or country. Moreover, there is already an Edit button to update records, when user click it another page open for the record to be updated and whatever he wants to update, it updates.

Hope everone understand the scenario.

I don't want to disturb the already provided Edit functionality and want to provide same record to be updated.

Any idea in this regard would be appreciated.

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