Converting from a Stored proc to SSIS? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Converting from a Stored proc to SSIS?

Author

Topic

lebz
Starting Member

7 Posts

Posted - 2011-02-07 : 05:41:57

We execute the stored proc each night with the following command:

declare @EndDateTime varchar(10)

set @EndDateTime = convert(varchar(10),dateadd(d,-1,getdate()),120)

exec sp_House_Keeping @EndDateTime ,'Yes',4

What we have found, is that the DB log file is growing too extreme sizes while the job is running. We know that this is because we are doing a bulk delete in a single transaction and all of the deleted rows end up in the LDF file in case the transaction requires a rollback.

One of the workarounds, is to use either a while loop or a cursor to delete each record in its own transaction.

Please can you convert this current stored proc into a SSIS package, having individual steps in SSIS for each step in the stored proc.Please can you convert this current stored proc into a SSIS package, having individual steps in SSIS for each step in the stored proc and that will also solve our Log file problem (deletes running in cursor or while loops). Do not worry too much about the current PRINT commands that is used. We do not require this anymore.

/****** Object: StoredProcedure [dbo].[sp_House_Keeping] Script Date: 02/03/2011 18:04:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_House_Keeping]
@EndDate datetime = null,
@Keep4DA varchar(3) = null,
@Days4DA int = null
AS

SET NOCOUNT ON

create table #todel (
msgid varchar(50)
)

print '-*-*-*-*-*-*-*START-*-*-*-*-*-*-'
print '------' + convert(varchar(24),getdate(),120) + '-------'
print '--------------------------------'

-- If no parameters were set set defualts
IF @EndDate is null
SET @EndDate = convert(varchar(10),getdate(),120)
IF @Keep4DA is null or @Keep4DA = 'No'
select @Days4DA = 0, @Keep4DA = 'No'
IF @Keep4DA = 'Yes' and @Days4DA is null
SET @Days4DA = 14

print 'Date : ' + convert(varchar(24),@EndDate,120)
print 'Keep 4 DA : ' + @Keep4DA
print 'Days 4 DA : ' + convert(varchar(10),@Days4DA)

insert into #todel
select originalmessageid from activemessages where timestamp < @EndDate
print 'To Delete : ' + convert(varchar(20),@@ROWCOUNT)
delete #todel
from #todel
join retrymessages on msgid = originalmessageid
print 'In Retry : ' + convert(varchar(20),@@ROWCOUNT)

delete #todel
from #todel
join incompletemessages on msgid = messageid
print 'Incomplete : ' + convert(varchar(20),@@ROWCOUNT)

IF @Keep4DA = 'Yes'
BEGIN
delete #todel
from #todel
join
(select
a1.originalmessageid
from activemessages a1
left outer join activemessages a2 on a1.originalmessageid = a2.referencemessageid and a2.messagetype in (25, 28, 47)
where a1.messagetype in (23, 26, 45)
and a1.timestamp < @EndDate
and a2.originalmessageid is null
and a1.timestamp > dateadd(dd,-@Days4DA,@EndDate)
) no_da on msgid = originalmessageid
print 'No DAs : ' + convert(varchar(20),@@ROWCOUNT)
END

print '-----------DELETING-------------'

delete am
from activemessages am
join #todel on originalmessageid = msgid
print 'AM : ' + convert(varchar(20),@@ROWCOUNT)

delete mr
from messagerecipients mr
left outer join activemessages am on mr.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'MR : ' + convert(varchar(20),@@ROWCOUNT)

delete mrs
from messagerecipientstates mrs
left outer join activemessages am on mrs.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'MRS: ' + convert(varchar(20),@@ROWCOUNT)

/* commented out so that we have a history of the exception...
delete ex
from messageexception ex
left outer join activemessages am on ex.originalmessageid = am.originalmessageid
where am.originalmessageid is null
print 'EX : ' + convert(varchar(20),@@ROWCOUNT)
*/

drop table #todel

print 'Re-Indexing...'
DBCC DBREINDEX(activemessages)
DBCC DBREINDEX(messagerecipients)
DBCC DBREINDEX(messagerecipientstates)
DBCC DBREINDEX(messageexception)

print '--------------------------------'
print '------' + convert(varchar(24),getdate(),120) + '-------'
print '-*-*-*-*-*-*-*DONE*-*-*-*-*-*-*-'

SET NOCOUNT OFF
GO

tshepo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-07 : 05:49:33

Why would you expect people to do your work for you for free?!?

How many emails are you sending out a day to make your logfile grow beyond endurance? Do you spam the world on a daily basis?

You don't need SSIS for this, you just need to write a loop.

WHILE EXISTS ( your criteria )
BEGIN
<do your delete (you can use TOP (n) to help with this)>
END

lebz
Starting Member

7 Posts

Posted - 2011-02-07 : 07:35:07

about 100 emails per day.could you write the loop please.thankx

tshepo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-07 : 08:54:24

No, I am not your slave. I work for a living and give my time here for free.

If you can not work out how to do it, employ someone who can.

Oh, if your logfile are growing too big with just 100 messages a day, you have more serious issues or you have far too little disk space..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 09:58:13

quote:
What we have found, is that the DB log file is growing too extreme sizes while the job is running.

For 100 emails? i think your problem lies elsewhere.

Expecting others to simply write code for you, for free...is a little much. You are not really asking for help...you have asked us to do your job for you. Good luck with that.

Poor planning on your part does not constitute an emergency on my part.

lebz
Starting Member

7 Posts

Posted - 2011-02-07 : 10:33:29

ok i hear you, lets forget the email part. lets say 10 emails. Please assist where possible. I nreally need to know how to convert from a stored proc to SSIS.

tshepo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-07 : 11:28:11

why do you need SSIS at all? I have already shown you how to do this in SQL, but it seems you either did not follow it or you still expect someone to do this for you.

If you really want to learn SSIS, pick up a book..

lebz
Starting Member

7 Posts

Posted - 2011-02-07 : 12:18:00

ok thanx. I get your point. is this the answer:
WHILE EXISTS ( your criteria )
BEGIN
<do your delete (you can use TOP (n) to help with this)>
END?
i need more information.could you show me the steps to cobert this into SSIS perhaps please i beg you.

tshepo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-07 : 12:21:26

Whats the fascination with SSIS still?

SQL has a great thing called Books Online. If you drop your fascination with SSIS for a moment and actually look up the words in CAPS, you might learn something. If you can find them in BoL, then google will help you, look for deleting in batch, sql server..

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 14:07:04

It makes no sense to convert from a stored procedure to SSIS based on what you describe.

Poor planning on your part does not constitute an emergency on my part.

lebz
Starting Member

7 Posts

Posted - 2011-02-08 : 03:37:11

okay i get you.

how do u convert this in to SSIS?

declare @EndDateTime varchar(10)

set @EndDateTime = convert(varchar(10),dateadd(d,-1,getdate()),120)

exec sp_House_Keeping @EndDateTime ,'Yes',4

tshepo

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-02-08 : 05:27:44

I give up...

Create a SQL task and put your bl**dy code in the SQL task, Then you will have your prized SSIS piece of carp instead of the prefectly resonable T-SQL piece of carp you have at the moment.

Good luck in the future..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources