Limitation with YEAR() function in SSIS

I learned something new today while researching an ETL failure:
The YEAR() function in SSIS has a lower limit of 1-1-1753.

Even though the DT_DATE and DT_DBDATE support dates earlier than 1-1-1753, the YEAR() function does not and will fail.

For validation in a derived column transformation, I was using this logic:
YEAR(data_field) < 1900? (DT_DBDATE)"1-1-1900":data_field

And when the date 8-23-0203 came thru, the package failed. The error was non-descript:

An error occurred while evaluating the function.

So, be aware...

peace

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

Quick T-SQL Tip #2: Identity Columns Seed Value

This post really deals with high volume tables with an identity column, but the idea is something that everyone should at least be familiar with.

Quite often I see table creation scripts that start like this:

Create Table ATable
(
ID int not null identity(1,1)
...

And while that is perfectly legal and will work with no issues, if this table is going to be a high volume table, you should consider using the entire Int, not just half of one.

What??

The Int data type spans -2,147,483,648 thru 2,147,483,647.  If your seed is 1, you lose 2,147,483,649 values that could be used.

I have seen production situations where they have run out of identity values because the seed was 1.
So, next time you create an Identity, ask yourself: "Two billion, or four billion?"

and then 

Create Table ATable
(
ID int not null identity(-2147483648,1)
...

peace

Quick T-SQL Tip #1 - Formatting SQL

I am going to start a series of posts for quick T-SQL tips.  In my current position, I support a group of business report developers that are not too familiar with T-SQL.  This will help them learn some of the tips and tricks that I use frequently.

So, tip #1, Formatting SQL.

This tip does not just apply to T-SQL, but any kind of code in general.
I am sure most other developers have been here:  You inherit some code and the first hour is spent trying to figure out what it's doing!

Well, here is my basic formatting.  My goal is to let anyone look at it and understand it quickly!


Select tab Field1 As Field1Name,
tab tab Field2 as Field2Name
From tab Table1 T1
Inner join
tab tab Table2 T2
on tab tab t1.Field = T2.Field
Where tab T1.Field = 'AA'
Order by
tab tab T2.Field1 ASC

Doesn't look that pretty there, but in SQL Management Studio, it works like a charm!  :)

My goal is to have enough whitespace that the SQL does not look cluttered.
No matter how you format, always keep the next developer in mind!!

peace