{"id":272,"date":"2010-12-19T09:43:09","date_gmt":"2010-12-18T23:43:09","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=272"},"modified":"2013-06-24T14:55:38","modified_gmt":"2013-06-24T04:55:38","slug":"ssis-date-conversion-error-going-from-oracle-to-sql-server","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2010\/12\/19\/ssis-date-conversion-error-going-from-oracle-to-sql-server\/","title":{"rendered":"SSIS Date conversion error going from Oracle to SQL Server"},"content":{"rendered":"<div id=\"ntsbl-2377664801\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>Hi,<\/p>\n<p>The other day I had the task to move a large amount of data from Oracle to SQL Server.<br \/>\nI had chosen to import the data via SSIS, doing a direct copy from one table in oracle to one table in SQL Server.<\/p>\n<p>Everything was fine until I one of my tables crashed, with a data conversion error.<\/p>\n<p>The issue is that the lowest date that SQL Server will accept is &#8220;1-Jan-1753&#8221;. There was some bad data in Oracle dated year 1049, consequently SQL server did no know how to handle it. How was I going to handle it?<\/p>\n<p>The solution is to use a transformation script. In your data flow task, add a transformation script between your source and destination. When you drop in on the form it pops up a dialogue, leave it default for transform.<\/p>\n<p>On the inputs column choose the date column in question (for the example lets assume its called CreatedDate).<br \/>\nMake sure you change its &#8220;UsageType&#8221; in the grid from &#8220;Read&#8221; to &#8220;Read-Write&#8221;.<\/p>\n<p>Go to the script tab and click &#8220;Design Script&#8221; enter something like the following code:<br \/>\nNote: This is in vb .net. I think 2008 R2 allows c#, but you should get the idea:<br \/>\n[csharp]<br \/>\n        Try<br \/>\n            Dim dt As DateTime = Row.CreatedDate()<br \/>\n            Dim dtMin As DateTime = System.DateTime.Parse(&quot;1753-01-01 00:00:00&quot;)<br \/>\n            Dim dtMax As DateTime = System.DateTime.Parse(&quot;9999-12-31 23:59:59&quot;)<br \/>\n            If (Row.CreatedDate_IsNull = False) Then<br \/>\n                If (dt &lt; dtMin Or dt &gt; dtMax) Then<br \/>\n                    If (dt &lt; dtMin) Then<br \/>\n                        Row.CreatedDate() = dtMin<br \/>\n                    Else<br \/>\n                        Row.CreatedDate() = dtMax<br \/>\n                    End If<br \/>\n                End If<br \/>\n            End If<br \/>\n        Catch ex As Exception<br \/>\n            Row.CreatedDate_IsNull = True<br \/>\n        End Try<br \/>\n[\/csharp]<br \/>\nThis code will find any row that has a date that falls outside the bounds of what SQL server will accept and set it to the Max or Min date values.<\/p>\n<p>Finally some general tips that I learned in attempting to move large amounts of data from Oracle to SQL.<\/p>\n<p>1. Don&#8217;t use a linked server connection it is really slow.<br \/>\n2. The Microsoft OLEDB connector for Oracle is pretty fast.<br \/>\n3. When setting up the OLEDB Destination via &#8220;Table or View&#8221; make sure you choose the &#8220;Fast Load&#8221; option!! Why you would want it to run slow is beyond me but the &#8220;Table or View &#8211; fast load&#8221; is about 10 to 15 times faster.<\/p>\n<p>Cheers<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, The other day I had the task to move a large amount of data from Oracle to SQL Server. I had chosen to import [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[13],"tags":[],"class_list":["post-272","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/272","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=272"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/272\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=272"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=272"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=272"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}