truncate based on condition

Question

truncate based on condition

komal145

SSCrazy Eights

Points: 9915
More actions
June 8, 2016 at 9:59 am

#311056

I have a package where I loop through different accounts.
I have a Master table where I set the status of flag to 0 or 1 if the package runs for that account for that date then updating that flag to 1 for success 0 for failure
i want to truncate the table for each account in stage based on that flag status , how do we do that in ssis?

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Just to be clear, your not truncating this mater table? Are you saying that each account has it's own table and that you want to truncate that table based on the flag? If so, is the table name stored in the Master Table along with the account flag?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

komal145 SSCrazy Eights Points: 9915 More actions · Answer 2

Master table only updates the account ran successful or not for particular date.

Master table format

Account date statusflag

12345 2016-04-02 1

67890 2016-04-02 0

There is a table which has data for these two accounts with dates

so need to check master table and check if any of the account is failure then do not truncate else truncate

komal145 SSCrazy Eights Points: 9915 More actions · Answer 3

I have a execute task beginning of the logic in which i have this logic

Declare @Truncate bit

BEGIN TRY

EXEC [GetLoadStatus]

@Query = N'accountname',

@StatusFlag = @Truncate OUTPUT

IF @Truncate = 0

BEGIN

Truncate Table TableA

END

END TRY

Storedprocedure takes accountname as input and get the statusflag as output

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Is it the same table that gets truncated regardless of the account?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

So your problem is solved?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

komal145 (6/8/2016)
I have a execute task beginning of the logic in which i have this logic
Declare @Truncate bit
BEGIN TRY
EXEC [GetLoadStatus]
@Query = N'accountname',
@StatusFlag = @Truncate OUTPUT
IF @Truncate = 0
BEGIN
Truncate Table TableA
END
END TRY
Storedprocedure takes accountname as input and get the statusflag as output

Again, does each account have its own table or are all accounts stored in TableA? If the latter, then you don't want to truncate as that deletes everything, EVERYTHING.

komal145 SSCrazy Eights Points: 9915 More actions · Answer 7

all accounts are stored in TableA witrh dates specified

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

komal145 (6/8/2016)
all accounts are stored in TableA witrh dates specified

Then TRUNCATE TABLE TableA will do just that, truncate the table of all data regardless if any of the data should be kept. You need to us a DELETE with a WHERE clause to delete the appropriate data that needs to be deleted.