SSISDB Object Reference not set to an instance of an object

Question

Hi,

Can you help discover possible reasons why that when I execute a SSIS from VS2017 the process is running, but when I deploy it to the SSISDB and running the same package the process return a error: "Object Reference not set to an instance of an object".

Package: Basically, this package has a Script Task with "DirectoryServices" component configured. (Again, when this package running from my computer, the process running with success!)
Environment: SQL instance is configured with SQL 2019 Enterprise Edition;

This error shoudl be caused for a oldest framework version in this SQL environment?

What you think?

Below here my script:

string userId = Dts.Variables["User::CreatedBy"].Value.ToString();
string[] userPart = userId.Split('\\');

try
{
    string entry = userPart[0].ToString(),
    account = userPart[1].ToString();

    // get a DirectorySearcher object
    DirectorySearcher search = new DirectorySearcher(entry);

    // specify the search filter
    search.Filter = "(&(objectClass=user)(anr=" + account + "))";

    // specify which property values to return in the search
    search.PropertiesToLoad.Add("mail");        // smtp mail address

    // perform the search
    SearchResult result = search.FindOne();
    DirectoryEntry user = result.GetDirectoryEntry();
    Dts.Variables["User::vEmail"].Value = user.Properties["mail"].Value.ToString();

    Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
    Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);

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

Thanks for your help!

André

Answer 1

Hi @André Ferraz Ramos ,

Obviously, the .Net Framework version shall be the same as defined for a Script Task in Visual Studio on dev. machine.

Though the following three lines (##19-21 in the original code) are questionable:

SearchResult result = search.FindOne();  
DirectoryEntry user = result.GetDirectoryEntry();  
Dts.Variables["User::vEmail"].Value = user.Properties["mail"].Value.ToString();  

You need to make sure that the search is successful and the returned user variable is not null.

Don't believe your eyes. It is a bug in VS, it doesn't remember what .Net Framework a Script Task was compiled with. And it sets that dropdown value to its default depending on the SSIS version.

You can check the .Net Framework directly by lookin inside the *.dtsx file.
It is a textual file in XML format just with the dtsx extension

You need to look for the following tag:

<TargetFrameworkVersion>v4.5</TargetFrameworkVersion>  

My other project/package shows the following (the default .Net was 4.7 originally):

<TargetFrameworkVersion>v4.7.2</TargetFrameworkVersion>  

That value shows a real .Net Framework version you manually selected.

Answer 2

Hi @Yitzhak Khabinsky , thank you for your reply!

I have this same code running in a different environment normaly.... Maybe should is the framework!

You know how can I set different framework in Script Task?
I did a tentative, to set a oldest (4.5.2) framework version, but unfortunately the VS setback to 4.7 automaticaly.....

Thanks,

Answer 3

Hi @André Ferraz Ramos ,

Can you help discover possible reasons why that when I execute a SSIS from VS2017 the process is running,

Environment: SQL instance is configured with SQL 2019 Enterprise Edition;

Please set the Target Server Version as SQL Server 2019 in ssis package.

Best Regards,
Mona