• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

SSIS custom task: Property is not getting value

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
0
Daniel Wilson
Asked:
Daniel Wilson
  • 7
  • 7
2 Solutions
 
HoggZillaCommented:
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
0
 
Daniel WilsonAuthor Commented:
At design time, the variable does get a default value

sendMailVariables.GIF
0
 
Daniel WilsonAuthor 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HoggZillaCommented:
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 = ?
0
 
HoggZillaCommented:
Perfect! Glad you got it.
0
 
Daniel WilsonAuthor 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!
0
 
HoggZillaCommented:
Just verify the Configuration table exists on the Test database, same Connection Manager name as was used in Dev.
0
 
Daniel WilsonAuthor 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!
0
 
HoggZillaCommented:
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.
0
 
Daniel WilsonAuthor Commented:
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.
0
 
HoggZillaCommented:
Curious, can you explain more about losing the Expressions? What does that mean?
0
 
Daniel WilsonAuthor 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.
0
 
Daniel WilsonAuthor Commented:
0
 
HoggZillaCommented:
Daniel, you have to remove the "close" request to award points. Choose Multiple Solutions.
Thanks, Hogg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now