2 Digit Date Format in SSIS

If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')

But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.

There is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049.

SSIS uses the Windows settings. The work around is to use derived column and get what you wanted as shown below:

(DT_DBTIMESTAMP) (SUBSTRING(CostPriceEndDate, 1, 7) + ((DT_I2)SUBSTRING(CostPriceEndDate, 8, 2) > 10 ? "19" : "20") + SUBSTRING(CostPriceEndDate, 8, 2))

**** PLEASE READ BELOW IF YOU HAVE SQL 2005 SP1 ****

http://wiki.sqlis.com/default.aspx/SQLISWiki/DateConversions.html
http://blogs.conchango.com/jamiethomson/archive/2006/04/26/3870.aspx

Thanks
Sutha

SQL 2005 Service Pack 1 and April Books Online Available

Service pack1 has been available for download few days now. Brian Knight has listed the enhancements / features related to SSIS which can be found here. You can get the full details from the following knowledge base articles KB 913090 and KB 916940.


SQL 2005 Books Online (April 2006) is available now for download.

Thanks
Sutha

Persistent Teams Up With Microsoft SSIS

Following up my earlier post about Oracle connector performance within SSIS, Persistent have released a press release, which can be found here. They have implemented their custom component at Moffit Cancer Centre.

Thanks
Sutha

Microsoft Bought ProClarity

Microsoft annouced yesterday that they have acquired Proclarity. You can access the press release here.

Where does this leave Panorama and IntelligentApps. Microsoft might give at least some of it free.

Very interesting move by MS.

Thanks
Sutha