SSIS – import csv with UTF-8 encoding to avoid weird characters

Possibly save 1 hour of your time: After you import a csv data file into your SQL Server database, you realize some data has funny characters.

That is because your csv file is probably encoded in ANSI. Make sure your original file is in UTF-8 encoding and ensure all characters appear as normal.

You can fix this in notepad as follows. I change the xA0 to a space and save as encode in UTF-8.

encode utf-8

Book: Professional Microsoft SQL Server 2012 Integration Services


SSIS – Script component is executed in wrong order or multiple times

Possibly save 1 hour of your time: This actually happens when I copy the package file.

The Script Component id remains the same. Hence it was executing a previous package code. It is documented here by Microsoft:

One resolution mentioned there was to delete the script component and paste it again. It didn’t work for me. The id was still the same as before. You can see the id when you open in edit mode and it is near the top of the window.

The resolution for me is to recreate the script component. I just have to redo it unfortunately.

Book: Professional Microsoft SQL Server 2012 Integration Services

Add nested dependent web.config transformation

Possibly save 1 hour of your time: When you want to do a continuous integration build, you may want to specify different web.config or app.config used for the transformation based on a specific environment configuration.

In my environment, we used web.config for local use. Then we have SYST for system integration environment (team check in), UAT for user acceptance testing environment and finally PROD for production environment.

So you add a web.SYST.config and it is not nested under the web.config. It should look like this.


Some people suggest right click on the Web.config and find Add Config Transform. That did not show up for me.

The alternative is to go into the csproj file directly and edit the include to have DependentUpon element.

content include.png

You also need to right click on the Solution and choose Configuration Manager. Under Active solution configuration, you can select New to create your own configuration. The name used here should also reflect the name in the config file.

configuration manager.png

Book:Murach’s ASP.NET 4.6 Web Programming with C 2015

SSIS – cannot create null column

Possibly save 1 hour of your time: When you have a data task flow that imports data from a flat file source and into a OLE DB destination database, you may encounter this issue where it fails to create the record because some columns have default values.

On your OLE DB destination, you must use Data Access mode of Table or view instead of Table or view (fast Load). Fast load does not provide ability to create records with default values.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS cannot debug breakpoint in Script Component

No solution yet but provide some insights: When I am targeting SSIS 2012, I got this issue.

I have a Data Flow task that includes an OLE DB source and then link to Script Component. In the OLE DB source, I use a SQL command query which uses a WHERE clause. The WHERE clause seems to be the issue that causes the debugger not break into the next Script Component. After I remove it, it seems to debug in the next script component, but then this doesn’t help in my case as I need to filter some data source. I am not sure if this is a bug.

P.S> This issue only happens with Script Component, and not with Script Task.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS – import csv with commas in data fields

Possibly save 1 hour of your time: When you have a line in csv file that contains data with comma, the SSIS package may not parse it correctly.

i.e. country name, iso code, numeric code

“Bonaire, Saint Eustatius and Saba”,BES,535

You can use double quote to signify as text qualifier in the flat file connection manager.

text qualifier

This should parse as the fields correctly.

Book – Professional Microsoft SQL Server 2012 Integration Services

npm install error – eperm operation not permitted unlink

Possibly save 1 hour of your time: I am not sure exactly what causes this issue.

My environment:

  • Windows 10
  • node v8.9.1
  • npm v5.6.0


Possible Solution:

1. Some has try the following:

  npm install –no-optional

2. Others have downgraded to npm 5.3.

  npm install npm@5.3 -g

3. Remove npm and npm cache



But none of the above works for me.

4. The hard way is to rollback and start with a working state.

5. Finally, if none of above works, start a new app and slowly migrate over to see what changes cause the issue. This is a tedious and long process.


SSIS error 0xC0011008 “the package failed to load due to error error loading from xml”

Possibly save 2 hours of your time: If you are starting a new SSIS project in Visual Studio, you may encounter this error.

Note: If you get error 0xC0011008 such as “the package failed to load due to error error loading from xml”, ensure you are targeting the right version of database via project properties. Right click the Project (not Solution) and choose Properties. In my case, I am using SQL Server 2012.


Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS – Failed to start project Exception deserializing the package “Value does not fall within the expected range”

Possibly save 1 hour of your time: I got this error after making some changes to packages.

I replace with version control one and still got this error. So I then look at other changes I have made.

I created lots of stuff. I have the luxury to revert my code based on source control. And the error goes away.

Later on as I add my code back, I discover the error has to do with SMTP connection manager used for sending email. I linked this connection manager to Send Mail Task Editor.

There is a bug when you setup your SMTP connection manager at the project level.



It will work if you change it to the package level.


After you check your version number using Add/Remove Programs, you can compare your version here at

I am running

  • SSDT 17.2 for Visual Studio 2015
    Build number: 14.0.61707.300


Book: Professional Microsoft SQL Server 2012 Integration Services