We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

SSIS custom task: Property is not getting value

Medium Priority
537 Views
Last Modified: 2013-11-10
We have developed a custom SSIS task to ... do some proprietary stuff.

It has a bunch of public properties, including
    < _
    Bindable(True), _
    Category("Email"), Description("URL ...")> _
    Public Property EmailWebServiceURL() As String
        Get
            Return sEmailWebServiceURL
        End Get
        Set(ByVal value As String)
            sEmailWebServiceURL = value
        End Set
    End Property

The Validate routine checks that this property is set:

        If Active Then
            If EmailWebServiceURL = vbNullString Then
                componentEvents.FireError(0, "Email Web Service Task", "You must provide the URL of the EmailWebService", vbNullString, 0)
                Return DTSExecResult.Failure
            End If
            'go validate more stuff
        end if

In our development environments, this works fine.  But on our test environment, the package that calls this component is getting the error above -- "You must provide the URL ..."

Same component, same package.  

As shown in the attached picture, the properties are being set from user variables.

On the TEST environment, we have traced the SQL statement that retrieves the value for the variable.  Rerunning that statement yields the value we expect.

What could be causing this problem?

Thanks!
SendMailProperties.GIF
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Hi Daniel,
The obvious answer is the variable User::EmailWebServiceURL has no value. How is this variable populated? If it is defined at design time, are there any other processes which could change the value?
Also check your script logic. How is this working? Any way it could be looking for something "server based"? sEmailWebServiceURL = value

Author

Commented:
At design time, the variable does get a default value

sendMailVariables.GIF

Author

Commented:
Oops, hit Submit too soon.

>>are there any other processes which could change the value?

Yeah, in the Package Configurations.

On the TEST environment (the one that's failing), we have traced the SQL statement that retrieves the value for the variable.  Rerunning that statement yields the value we expect.

packageConfigWizard.GIF
CERTIFIED EXPERT
Commented:
I state the obvious, as you already know this. But apparantly that value is not where expected at runtime.
Option 1: Since this works in other environments, my guess is the URL is being set in the process - possibly by a registry entry or other package configuration object. Check that. Put a Script Task with a msgbox just before the custom task, display the value fo the variable.
MsgBox(DTS.Variables("EmailWebServiceURL").value.tostring)
Option 2: What is the SET statement for the property in the component?
 Public Property EmailWebServiceURL() As String
        Get
            Return sEmailWebServiceURL
        End Get
        Set(ByVal value As String)
            sEmailWebServiceURL = value
        End Set
    End Property
??? .EmailWebServiceURL = ?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:
Perfect! Glad you got it.

Author

Commented:
No, I haven't got it!

I know where the data are supposed to be coming from ... and the data are there!  But this element is not getting into the property.

Once we get to our TEST environment, we can't use most of our regular tools to troubleshoot.  Looks like your Option 1 or similar may be our only choice ... but if you can think of anything that would CAUSE this, that would be good as our promotion process to get a revised package to TEST will eat a week and a half.

Thanks!
CERTIFIED EXPERT

Commented:
Just verify the Configuration table exists on the Test database, same Connection Manager name as was used in Dev.

Author

Commented:
yeah, that's been verified.

We've even pointed the TEST SSIS packages at the DEV database instance to rule out such a data issue.  The TEST SSIS package fails even looking at the DEV DB.  Conversely, DEV SSIS packages succeed when looking at the TESt DB.

We get success when the custom component is installed somewhere other than TEST.

We get failure when the custom component is installed on TEST.

And the DB at which it looks is independent of that.

A couple guys have been suggesting rights issues ... but since the custom component is instantiated I find that one hard to believe.

Further ideas?

Thanks!
CERTIFIED EXPERT

Commented:
Assumptions:
1. The connection manager used for the Configuration is pointed where you think it is.
2. The user running the package has access to the Schema on the configuration database.
3. If you put a msgbox in the form to display the URL, the data for the URL will NOT be there because the problem is in the configuraiton.
Ideas:
1. Put an Execute SQL Task in the package and select the URL from the configuration table. Use the same connection manager used by the Configuration. Select the value and put it into another variable. Put a msgbox to display that value.
When we deployed to the TEST environment, we lost the Expressions.  Without Expressions, having all the variables loaded did us no good ... b/c they didn't get loaded into the component's properties.
CERTIFIED EXPERT

Commented:
Curious, can you explain more about losing the Expressions? What does that mean?

Author

Commented:
We got an admin to put Visual Studio on the Test server.  It took long enough to figure out ... but eventually we saw that if you click on the task, go to Properties, and expand Expressions -- they were empty.

The first screenshot I posted shows how it looks on our workstations and on the DEV clusters.
CERTIFIED EXPERT

Commented:
Daniel, you have to remove the "close" request to award points. Choose Multiple Solutions.
Thanks, Hogg
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.