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

Advertisements

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;
}

SQL Server import wizard treat blank as null instead of zero 0

Possibly save 1 hour of your time: There are times when you need to import csv file with a nullable integer column. However, when you use the SQL Server database >> Task >> Import wizard, you will notice the result shows integer 0 instead of null values when the import csv file shows blank.

There is no feature on this wizard to treat blanks as nulls. The alternative is to use SQL Server Integration Service (SSIS).

But you can do it via an SSIS package to do ETL on the data from the CSV file into a SQL Server database.

You can use Data Flow Task. Inside the Data Flow Task, you can have a Flat File Source and an OLE DB Destination.

import csv to database.png

Click on OLD DB Destination, and there is option to Keep nulls.

OLE DB Destination - keep nulls

If you don’t know SSIS, you can always use the import data feature. In the last step, there is an option to Save SSIS Package and choose File System and the next screen will ask where you want to save. After you saved, then you can load that into a SQL Server Integration Services project.

save ssis package

Book:Professional Microsoft SQL Server 2012 Integration Services

FTP Task – FTP Import: Error: Directory is not specified in the file connection manager $Package-variableName

Possibly save 4 hours of your time: I notice some of my SSIS jobs are no longer working and the errors are different for each of them. But one error gave clue about the package variable issue.

When I run the package directly using environment variables, it works fine. But when I use a job with proxy/credential identity, it failed. So there is another hint this is related to permission issue.

What caught me off guard was these jobs used to work.

The jobs ftp files from/to the ftp server to a network location.

The variable in this case was a network path.

Solution:

We change the environment variable to point to a local path and it works. This identifies that the issue has to do with the identity used to run the job does not have access to the network location defined by the variable.

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

SSIS – An error occurred in the requested FTP operation. Detailed error description: The password was not allowed

Possibly save 4 hours of your time: When you deploy a package with FTP Connection Manager you can get this error in which it says the password was not allowed.

The SQL Server Agent job is set up to use environment variables and the variables are also setup. However the job still fails when it try to connect to the FTP server. So for some reason the Environment variables are not working. The following environment variables are there (i.e. naming convention may be different from yours)

  1. FTPConnection_ServerName
  2. FTPConnection_ServerPort
  3. FTPConnection_ServerUserName
  4. FTPConnection_ServerPassword

The problem was that these variables are not directly used by the FTP Connection Manager in the package yet. You can right click the FTP Connection Manager and choose Parameterize. By choosing the Use existing parameter, that will link to the package parameter used in this package.

Parameterize right click

Parameterize.png

P.S. > If you are not using environment variables in your deployment, then you may also want to change protection level by right click Properties on the project and the package to Do not save sensitive data. However this is not recommended. Please use at own risk. There are other scripting solutions so that it is not required to deploy the password. Please google that.

do not save sensitive data

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

SSIS – Project Parameters, Package Parameters, User Variables

Possibly save 1 hour of your time: There are three types of parameters in SSIS that you can configure to use by your packages.

  1. Project parameters
  2. Package parameters
  3. User variables

You can access project parameters from the Team Explorer >> Project.params.

Project Parameters

Next you can access the package variables by double clicking the package to open the designer and choose tab Parameters.

Package Variables

Finally, you also have user variables. You can right click on the Control Flow designer and choose User Variables.

User Variables

Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

SSIS – Foreach Loop Container and Execute SQL Task

Possibly save 2 hours of your time: When you work on SSIS to do ETL, you may often encounter the need to loop through some records and process each record one at a time.
You can use the following steps:
  • Execute SQL Task – Get the records that need to be processed
  • Foreach Loop Container – Loop through each record
    • Execute SQL Task – Process one record at a time
ssis_foreachloop_1

Create two user variables:

  • i.e. CollectionIDs (Object) – One for collection of the IDs to be processed
  • i.e. CollectionID (Int32) – the current ID to be processed

 

The following will get the Full result set based on a select query. In this case as you can see on the SQLStatement, we select a primary key ID column from a table based on some criteria input. i.e. select ID from someTable i inner join anotherTable d on i.SourceID = d.SourceID where d.Title = ?

ssis_foreachloop_2

You may use a parameter input via Parameter Mapping in your query (i.e. select id from table where name = ‘someVariable’).

ssis_foreachloop_3

Now you want to store your Result Set into a user variable for looping through later.

ssis_foreachloop_4

In the Foreach Loop Editor, you can use Foreach ADO Enumerator, and select the collection user variable.

ssis_foreachloop_5

We want to a user variable to store the current processed id.

ssis_foreachloop_6

You can specify a query or stored procedure to process each record.

i.e. [schema].[usp_ProcessRecord] NULL, ?

The above example has two input variables where one is always NULL and the ? represents an input parameter.

ssis_foreachloop_7

In this example, the input is a large integer.

ssis_foreachloop_8