SSIS Ado.net connection Update SQL

  • I am connecting to an online MSSQL DB via an ADO.Net connection and running the data through an SSIS package. My question is how can I do an update statement back through the ADO.net connection. It is unlike the OLEDB connection that I am accustom to where it is direct and pretty simple.

    I currently have the results of the package going into a Recordset Destination where I have a couple of input columns and therefore should be going to my object variable. I then have my data flow going into a foreach loop container where the enumerator is Foreach ADO and my source variable is mapped to the same as the recordset. Pretty mush as described here: https://technet.microsoft.com/en-us/library/cc879316%28v=sql.105%29.aspx

    Inside my Foreach container I have an execute sql command(not email) that is like the one below.

    Update [Products]

    Set Value1 = 0,

    Value2 = ?

    Where Id = ?

    Do I have to setup the connection again in the Execute SQL Task? I'm thinking yes but I cannot get it to work. Doing an update statement should not be this hard. If someone can help me figure this out it would be greatly appreciated.

  • For one thing, updating the records one at a time via the For Each Loop container is a seriously bad way to approach an update of any sizable number of records. Also, why not just use an Execute SQL Task to handle the entire process? Is there some other intermediate requirement, or some inter-database or inter-server thing going on?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • There is nothing else going on that specifically requires me to do it that way other than that's the only way I know to get the information back out. This is my first experience with ADO.net connections. In my case, performance is not a big deal since the table is less than 1000 rows. What would be a better way to approach the problem? Create and populate a temp table in the Data Flow and then use the execute sql task to do the update? I appreciate the advice.

  • I think you have the right idea there... an EXECUTE SQL TASK could pretty much take care of the whole shootin' match, so to speak, assuming that the data exists and remains in the same server. I'm just looking at this from the perspective of what happens a few years down the road, when perhaps there are quite a few more records than exist today, or when a window in which this takes place has to shrink significantly. Efficiency today means less work down the road, and that's usually the best thing for any business. Without a lot of specific details about the WHERE for this data in terms of same server vs. different server, it's hard to know exactly what would work best. Usually, it's best to use SSIS to move data between servers or DB instances, and a simple SQL Script set up as a SQL Agent job could be enough to handle data when it's all on the same database server / instance. It will usually perform better as a single script rather than involving any kind of user-written looping mechanism. Does that help explain?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • I understand what you are saying and I agree. I'm my situation, I am updating product information on a remote sql server with data from a local sql server. My SSIS package does a sort of lookup on the remote server to determine is the information needs to be inserted or updated. The update portion is my problem and the reason for my questions. I will see about the creating the temp table, populating it and updating from it.

  • bwinchester (2/14/2016)


    I understand what you are saying and I agree. I'm my situation, I am updating product information on a remote sql server with data from a local sql server. My SSIS package does a sort of lookup on the remote server to determine is the information needs to be inserted or updated. The update portion is my problem and the reason for my questions. I will see about the creating the temp table, populating it and updating from it.

    You could use SSIS to move the data into the remote machine first into a tempDB table there. By that I mean you could create a table in the Temp database on the fly , populate it (add a PK ideally) and then use a EXECUTE SQL task to do you updates or use a MERGE statement to carry out your tasks. The table could be dropped at the end. Of course it would be nicer to have a permanent staging table. This is one idea.

    I do caution against doing a join across servers in case you wanted to host the temp table locally on your end.

    ----------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply