Mark White's BI Blog

Oracle and SSIS both have the ability to handle dates which SQL server's DateTime type won't accept. A reasonably simple solution is to leverage the script component to detect and repair the invalid dates... here's a step-by-step guide for doing exactly that... Add a script component to your data flow, in transform mode. Open the editor for the script component Under  Input Columns, select all the date columns you want to repair, and set their usage type to READ/WRITE (this is vital, else the script will break). Under Script , click the edit script button Select and delete all text Paste the C# code from here Close the source code window, click OK to dismass the script editor. Run the data flow. The script will efficiently detect date with bad years and attempt to correct them to something which SQL finds palettable. Give it a whirl and let me know what you think!

Building Business Intelligence using the Microsoft BI stack in an organization that has an Oracle based transactional system is not uncommon, and here I'll outline a couple of tips and tricks that should ease the building of SSIS packages in that type of environment.   Attunity Components Attunity offer several nice solutions for getitng data out of Oracle and into SQL - one of which is their CDC (Change Data Capture) offering . If you don't have the budget or stomach for setting up CDC in an oracle environment, then your next best bet is to use the free Attunity oracle connectors for SSIS , which demonstrate a measurable performance boost.   The Oracle Index and Order By Gotcha In previous posts I mention the performance benefits of loading sorted data into target tables. I'm currently loading data from Oracle 10, which exhibits a rather strange characteristic... Oracle does not use make us of an index for ordering data .  In other words, Oracle only uses ...