Error Calling Stored Procedure with DATE and TIME datatypes from SSIS

When calling a stored procedure using an OLE DB Command from SSIS to a stored procedure that has a DATE or TIME parameter, you will get the following error:

An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Operand type clash: int is incompatible with date"

The quick fix to this is to change the parameter type in the stored procedure to a varchar(50).
Some blogs recommend changing it to DATETIME, but the range of DATETIME is much smaller than DATE and will truncate.

peace

SQL Server Task Status

Have you ever been performing a DBCC SHRINKFILE and wondered how long it was going to take?  The Backup/Restore screen shows percent complete, how about DBCC CHECKDB?

Here's a neat query that can help with that:

Select * from sys.dm_exec_requests

This query returns information about every request that the SQL Server is executing.

One of the columns returned is Percent_Complete.  However, this column is only populated for the following commands:

ALTER INDEX REORGANIZE
AUTO_SHRINK option with ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
RECOVERY
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

I have four large databases that I have to shrink when they are copied to the development.  These can take quite a while, so I use this query to monitor status.

Check out the documentation for more details!

peace