Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4181
  • Last Modified:

SSIS: insert AND delete rows

How can I use SSIS to insert AND delete rows in a SQL Server table based on an an action code contained in each row of a flat file?  Each row in the flat file contains an ACTION_CODE, an ID, and other data.  If the ACTION_CODE is 'A' then a row in the SQL Server table is to be inserted containing data from the flat file row including the ID.  If the ACTION_CODE is 'D' then all rows in the SQL Server table with an ID matching the ID in the flat file row are to be deleted.
0
mikeyrad
Asked:
mikeyrad
  • 2
  • 2
  • 2
2 Solutions
 
YveauCommented:
I would import the complete flatfile to a temp table.
That way you can insert all records with action code 'A' at once and delete all records with action code 'D' at once. Two set based actions ... should be pretty fast .

Hope this helps ...
0
 
imitchieCommented:
can SSIS create temp tables using the GUI?
if you will use it often, create a table matching the flat file, which I assume is TargetTable + 1 column (Action_Code)
set up a normal SSIS job to first truncate the table, then import into the table
then add a custom SQL query something like

delete TargetTable where ID in (select ID from UpdateTable where Action_Code = 'D');
insert into TargetTable (ID,b,c,d,e,f)
select ID,b,c,d,e,f from UpdateTable where Action_Code = 'A'
0
 
mikeyradAuthor Commented:
It has to be done on a row by row basis.  I'm trying to use the Conditional Split Data Flow Transformation.  One branch goes to add records row by row, piece of cake.  I'm just stuck on what to do on the delete; Script, OLE DB Command?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
imitchieCommented:
I'm sorry can I ask why it must be done on a row by row basis?
Using custom SQL is more flexible for certain tasks.
0
 
mikeyradAuthor Commented:
Actually, it turns out a little more complicated because of the format and order of the flatfile, but the concepts were sound.
0
 
YveauCommented:
Glad I could be of any help.
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now