Best way to write an update procedure in Sql Server

I'm an Oracle developer learning Sql Server / SSIS.

Typically, I write update procedures in Oracle, read the d.b. with a cursor, loop through the cursor, then write an update statement. Then send an e-mail with totals, etc.

I'm also in a Sql Server shop that does a lot of SSIS work. I'm familiar with the tool but haven't actually written anything yet.

In my first discussion with an SSIS developer, his first reaction was SSIS might not be the right tool for something like this.

What's the best approach for this (cursor, loop through data, update, e-mail with totals) ?
LVL 1
Alaska CowboyAsked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

My first thought was to consume the flatfile and take into a table that matches it, then do one update rather than use a cursor. Maybe I'm wrong, but on the face of it a cursor doesn't appear necessary.

Regards
  David
0
 
Alaska CowboyAuthor Commented:
Another example is
- read a flat file
- use info in the flat file to do a lookup, returning another value based on lookup info
- update a d.b. record with the returned lookup value, based on fields in the flat file
- send e-mail with totals, etc.

So it's not really moving data from point A to point B with transformation in the classic sense, but it's reading data and then making an update to a d.b.
0
 
Alaska CowboyAuthor Commented:
David,

ok, that makes sense. But I'm more asking is SSIS the right tool ? If not, what is ?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

SSIS is a good choice to import a flat file to a SQL database table.

Subsequent to that I'd be using a SQL task and write the rest in a stored procedure called by the SQL task.

HTH
  David
0
 
Alaska CowboyAuthor Commented:
David,

ok, makes sense. I can separate out the file reading from the stored procedure, just trying to visualize the pieces before diving in.

thanks.
0
 
Alaska CowboyAuthor Commented:
David, sorry for the delay, closing now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.