SQL Destination or OLE DB Destination

Question

Destination database is MS SQL. So, I have used sql server destination in the package. I would like to know which is the best, SQL Server destination or OLE DB destination?

When I use MS SQL destination, I am facing few challenges  like datatype while importing data into MS SQL. How to handle this? I am using cast in query to fetch the data from source to desitnation. Please suggest me the best way of doing this activity.

Answer 1

OLEDB is better because the SQL Server destination requires privileged rights and therefore involves some system changes oftentimes.

Yet, the SQL Server Destination is for a local SQL Server Instance only.

The SQL Server connection is super fast though.

If you face some datatype issues please resort to using a Derived Column Transformation as shown here http://sqlblog.com/blogs/andy_leonard/archive/2009/01/31/ssis-expression-language-and-variables.aspx to convert/cast between datatypes.

---

Arthur My Blog

Answer 2

Why we need to declare or do cast / convert explicitly? Why BIDS will not automatically do cast / convert? Any particular reason for this?

Answer 3

If (for example) you are loading from a flat file, how to guess the data types? Another example is Excel, the top 8 rows get sampled and then the "decision" is made to use an arbitrary datatype which may be wrong.

To make story short, it guesses, but oftentimes datatype adjustments are necessary and the control is given to the developer in form of various components, luckily it is not too too hard to do.

---

Arthur My Blog