SQL 2005: DTUTIL Export SSIS Packages Very Slow

Erik G

unread,

Oct 17, 2009, 2:22:47 PM10/17/09

to

Can anyone tell me why this takes 10 hours for 400 packages? Both SQL
Server instances are 64-bit SQL Server 2005 Enterprise, its not the
cursor as the print statement will go by very fast. I'm using this
code as part of a DR plan and 10 hours is not acceptable to us. This
uses MSDB to MSDB copy. We tried MSDB to DTSX and then DTSX to MSDB
and it still took 10 hours. Thank you in advance for any help/
feedback.

I also have another 2 servers that can copy 100 packages between each
other in seconds:

DECLARE @srcServer as VARCHAR(24) -- Source server name
DECLARE @destServer VARCHAR(24) -- Destination server name
DECLARE @srcUser VARCHAR(24) -- SQL Server login used to connect
to the source server
DECLARE @srcPassword VARCHAR(24) -- Password of the SQL Server login
on the source server
DECLARE @destUser VARCHAR(24) -- SQL Server login used to connect
to the destination server
DECLARE @destPassword VARCHAR(24) -- Password of the SQL Server login
on the destination server
DECLARE @SQL VARCHAR(4096)

SET @destServer = 'SERVER2'
SET @srcServer = 'SERVER1'
SET @srcUser = ''
SET @srcPassword = ''
SET @destUser = ''
SET @destPassword = ''

DECLARE curExecSQL Cursor

FOR
-- Copy SSIS packages
select 'EXEC [master].[sys].[xp_cmdshell] ''dtutil /Quiet /COPY SQL;'
+
case foldername when '' then '"' + [name] + '"' else '"' + foldername
+ '\' + [name] + '"' end
+ ' /SQL ' + case foldername when '' then '"' + [name] + '"' else '"'
+ foldername + '\' + [name] + '"' end
+ ' /SOURCESERVER ' + @srcServer
+ case @srcUser when '' then '' else ' /SourceUser ' + @srcUser + ' /
SourcePassword ' + @srcPassword end
+ ' /DESTSERVER ' + @destServer
+ case @destUser when '' then '' else ' /DestUser ' + @destUser + ' /
DestPassword ' + @destPassword end + ''''
from
msdb.dbo.sysdtspackages90 pkg join msdb.dbo.sysdtspackagefolders90
fld
on pkg.folderid = fld.folderid

OPEN curExecSQL

Fetch NEXT FROM curExecSQL INTO @SQL
WHILE @@FETCH_STATUS = 0

BEGIN
PRINT 'Executing' + CHAR(10) + @SQL + CHAR(10) + '---------------'
EXEC(@SQL)

FETCH NEXT FROM curExecSQL INTO @SQL

END
CLOSE curExecSQL
DEALLOCATE curExecSQL
GO

John Bell

unread,

Oct 18, 2009, 4:30:03 PM10/18/09

to

"Erik G" <in...@onlyhd.tv> wrote in message
news:75e2f759-0bbc-4f3a...@g19g2000yqo.googlegroups.com...

Hi

I am not sure why this should take so long.

Have you checked that the routing and network performance between the two
servers is not an issue?
If you created a batch file from the script how long does that take to run
from a command prompt?
Are you short of resources for processes other than SQL Server?

John

Erik G

unread,

Oct 18, 2009, 5:12:59 PM10/18/09

to

Thanks John,

Zero load on two 16-way box each with 128GB of RAM, both in the same
switch. I have tried copying and pasting the commands outside the
cursor and I get the same performance; about 1 min 45 seconds per
transfer. Also, both server pack 3 (Build 4.0.4035).

Erik

John Bell

unread,

Oct 19, 2009, 1:09:28 AM10/19/09

to

"Erik G" <in...@onlyhd.tv> wrote in message

news:14c39068-8426-4ee1...@u16g2000pru.googlegroups.com...

Hi Erik

How does SQLCMD perform?

Have you tried a tracert and ping to determine network performance?

John

Erik G

unread,

Oct 19, 2009, 12:08:01 PM10/19/09

to

Pings in < 1 ms in a gigabit switch

Thanks

Erik

John Bell

unread,

Oct 20, 2009, 1:18:26 AM10/20/09

to

"Erik G" <in...@onlyhd.tv> wrote in message

news:bdeac1a9-3d78-455f...@j9g2000vbp.googlegroups.com...

> Pings in < 1 ms in a gigabit switch
>
> Thanks
>
> Erik

That sounds like the network is ok?

An time it takes to connect with SQLCMD?

Has the machine changed name?

John