SSIS and Max Packet Size

Working to improve my ETL performance, I changed my Max Packet Size on my database server and my ETL server to 32767. However, when I tried to run an SSIS package, I received the following error:

Communication Link Error.
Shared Memory Provider: No process is on the other end of the pipe.

I also received this error when in SQL Server Management Studio, connected to the Integration Services on that server, and tried to browse thru the package store to a package.
After a little research, I determined that SSIS cannot read the packages from the MSDB database if the the Max Packet Size is set larger than 16384.

So at this point, my options are to lower the Max Packet Size on the ETL server, Save my SSIS packages on the hard drive, or move the packages to a package store on a different server, with the Max Packet Size at 16384 or lower.

I am going to test this out and see if it warrants moving the packages to either the file system or another server, or if the 16384 packet size will be sufficient for our ETL processing.


peace

Rebuilding All Indexes on a Table

Sometimes you have a need to rebuild all indexes on a table.  This could be because of an ETL process, or other data loading process that renders the indexes out of date.
I recently experienced this issue on during ETL processing.

I could have scripted out all of the index rebuilds for the table and then added to an Execute SQL task within SSIS. However, if any index should be deleted or renamed, then the Execute SQL task would fail, and the ETL would stop. Also, if we added new indexes, they would not get rebuilt until we added the index to the the Execute SQL task.

So I wrote a stored procedure to rebuild all of the indexes on the a table, or, just one index on a table.

I wanted to share that procedure with you, since I think this may be a common scenario.

-- =============================================
-- spwRebuildTableIndexes
--
-- Author:        Bob Pearson
-- Create date: 2013-03-14
-- Description:    Rebuilds specified index or all indexes on a table
--
-- Updates
-- Date            Who        What
-- --------        ----    ----------------------------
--
-- =============================================
ALTER PROCEDURE [dbo].[spwRebuildTableIndexes]
    @TableName varchar(100),
    @IndexName varchar(100) = null

AS
BEGIN

    Set NOCOUNT ON;

    Declare @SQL varchar(max) = '';
   
    if @IndexName is not null
    BEGIN
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
        print @TableName + '.' + @IndexName + ' Rebuilt...'
        Return;
    END

    Declare cur cursor for

        Select    i.name
        from    sys.indexes i
        inner join
                sys.objects o
        on        i.object_id = o.object_id
        where    o.name = @TableName and o.type = 'U'

    for read only;

    Open Cur;

    Fetch Next from Cur into @IndexName;

    While @@FETCH_STATUS = 0
    BEGIN       
        Select @SQL = 'Alter Index ' + @IndexName + ' on ' + @TableName + ' REBUILD;';
        Execute (@SQL);
       
        print @TableName + '.' + @IndexName + ' Rebuilt...'
       
        Fetch Next from Cur into @IndexName;
    END
   
    Close Cur;
    Deallocate Cur;
END

peace