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

Advertisements

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

C:\Users\your.name\AppData\Roaming\npm

C:\Users\your.name\AppData\Roaming\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.

targetserverversion

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.

Bug:

https://social.technet.microsoft.com/Forums/WINDOWS/en-US/0fcddaaf-813c-48dc-a40b-c0f9aa396fa2/smtp-connection-level-project-doesnt-work?forum=sqlintegrationservices

Solution:

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

Versions:

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

https://docs.microsoft.com/en-us/sql/ssdt/changelog-for-sql-server-data-tools-ssdt

I am running

  • SSDT 17.2 for Visual Studio 2015
    Build number: 14.0.61707.300

 

Book: Professional Microsoft SQL Server 2012 Integration Services

SSIS – read a file based on file name parameter

Possibly save 1 hour of your time: There are times you will need to read a file and process the data in it.

In this scenario, there is an input for the file name with path and an output with the second line parsed.

 

public void Main()
{
			// TODO: Add your code here

                using (var reader = new StreamReader(Dts.Variables["SupportListFile"].Value.ToString()))
                {
//custom code to read first and second line
//read first line
                    reader.ReadLine();
//read second line
                    var list = reader.ReadLine();

//assign output variable
                    if (!string.IsNullOrEmpty(list))
                        Dts.Variables["SupportList"].Value = list;
                }

            Dts.TaskResult = (int)ScriptResults.Success;
}