Sql-server – SQL Server imports data in wrong charset with SSIS

If you're going to use SSIS, may I suggest you use it? ;)

What I believe you are attempting to do is insert some data that doesn't already exist in the target. You have identified what data needs to go using your min/max logic and you attempted to do a similar operation for the target.

For starters, the OLE DB Command is something you should use sparingly. It performs singleton operations and so the volume of rows you want this thing handling should be in the tens, maybe hundreds. Definitely not 80 million.

Your data flow should look something like this

I have an OLE DB Source (ADO.NET can be used but if you need to parameterize your query, it's a little more complex). You can use your existing query or if you need to chunk it up because you have active systems, so be it.

The Lookup Component, LKP RM_DimEmplo... is the key to this operation. When the package starts, it is going to run the source query in that component and cache all the values. That sounds expensive, so don't let it be. Don't just select dbo.destinationTable. Write your query to bring back only the keys you need to make a match. It should be those IDs. That will be very narrow and shouldn't be so bad. The idea here is that you will have your source rows and you will compare those to the cache of the target table based on those keys. Since you're not worried about detecting differences, you know that anything that doesn't match needs to go, so on the "General" tab, change the "Specify how to handle rows with no matching entries" to use Redirect rows to no match output. Otherwise, this will blow up when it doesn't make a match.

If you find it's still impactfull on your systems, then you need to go to a more advanced approach of using Expressions on the Lookup Component to filter the range of data to match the IDs coming in. Thus, you have identified the source range is 100 to 200. There's no need to pull in the full 0 to 1000 range of keys in the destination, you would simply make the lookup cache the keys that are in the operational range. 80M shouldn't be terribly taxing unless you have just no RAM to spare on the machine.

The OLE DB Command does singleton, one-off operations. The OLE DB Destination can either do singleton inserts (default for 2005 - Table or view) or set/batch based updates (Table or View - fast load) which can be screaming cheetah wheelies fast. Since you're loading a lot of data, unless you want your admins to hate you, change the Max insert commit size to something other than the default of 2147483647. That basically says don't commit nothing until it's all been inserted. That can cause your transaction log to get big in a hurry.

The nice thing about your problem is that you can run this to your heart's content. Say 1M rows get loaded and something bad happens. Just restart. However much got committed and when it restarts, it might pull the same source data but your lookup will have the most recent set of keys from the target so the result will be no operation performed for those. I know, you'd think everyone loves duplicates in their target system but for some reason, that just doesn't fly at some places...

Wrapup

I can't recommend the Stairway to Integration Services series enough. Specifically for this problem, Level 3

You have to deal with the unicode replacement character by calling it what it is:

Row.Description = Row.Description.Replace(NCHAR(65533), "a")

EDIT:

I apologize as I first gave you a pseudo-SQL Server solution, but with some additional research learned that SSIS behaves a little differently. I'm linking my source that gives multiple solutions, and for an answer will include the first. It seems that you could add a derived column component that would allow you to add a new column (e.g. strDescription from your table using the Type Cast operator DT_STR which will convert the unicode to string. You should then be able to use this derived column in place of the original column in you CSV destination. source Hope that helps!