[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SSIS custom task: Property is not getting value

Posted on 2009-02-11
14
Medium Priority
?
498 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
0
Comment
Question by:Daniel Wilson
  • 7
  • 7
14 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23614655
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23614799
At design time, the variable does get a default value

sendMailVariables.GIF
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23614897
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Assisted Solution

by:HoggZilla
HoggZilla earned 2000 total points
ID: 23614900
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23614912
Perfect! Glad you got it.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23615049
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23615105
Just verify the Configuration table exists on the Test database, same Connection Manager name as was used in Dev.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23615418
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23615559
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
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 0 total points
ID: 23637220
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23637297
Curious, can you explain more about losing the Expressions? What does that mean?
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23637437
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
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 23637510
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 23639184
Daniel, you have to remove the "close" request to award points. Choose Multiple Solutions.
Thanks, Hogg
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question