Solved

SSIS: insert AND delete rows

Posted on 2007-11-21
6
4,133 Views
Last Modified: 2010-04-21
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
Comment
Question by:mikeyrad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 300 total points
ID: 20331369
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 200 total points
ID: 20333066
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
 

Author Comment

by:mikeyrad
ID: 20369538
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 25

Expert Comment

by:imitchie
ID: 20370143
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
 

Author Closing Comment

by:mikeyrad
ID: 31410482
Actually, it turns out a little more complicated because of the format and order of the flatfile, but the concepts were sound.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20387471
Glad I could be of any help.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question