Can someone point me to a comparison between the OLE DB data source and the Data Reader Data Source
We are starting to investigate SSIS and have stumbled through some pain points by choosing one or the other.
Additionally, I found that one can not use the SQL Server Destination unless the package is actually running on the Server hosting the SQL Instance. This seems like we are moving backwards in time. We have been able to run DTS packages on a client machine that doesn't host either source or target data sources.
The SQL Server Destination seems like the most intuitive destination to use if my target is a SQL Server, so I wondered if this was a bug, or by design
Regards,
Eric
SSIS Data sources and destinations
GazCorb
As Douglas said the DataReaderSource is a .NET source. .NET only knows Unicode so it can only provide Unicode strings.
Thanks,
Matt
nitinca
Eric,
You should think of the OLE DB Destination as the de facto method of loading to...well...any database really. The SQL Server Destination is there for special circumstances. Check BOL out for all the details.
-Jamie
onesqlguy
Regarding the datasources,
What you are saying makes sense. Does the Datareader want to treat all strings solely as unicode. I have an example where my table has a char(6) column that the datareader converts to a unicode string with a length of 6 in the output column. I have tried to go into the advanced editor on the data reader to modify this setting to use string and not unicode string, but continue to get an error saying I can't do this. Has anyone else seen this
Jarret
As to the sources, if you come from a programming background, it may help to think of the OLE DB data source as the ADO source that uses native providers, and the DataReader data source as the ADO.NET source that uses managed providers.
The SQL Server Destination is optimized for use on the local machine. I wish that it had been possible to come up with an equally short name that let users know about the "same machine" limitation. To load to a remote machine, you would use the OLE DB destination, which should meet all of your needs.
-Doug