package with a custom component (log provider) runs in BIDS, but doesn't run where deployed.

Hi,

I have a package with a custom log provider, which runs in BIDS. However when I deploy the package onto SQL Server and run it on the deployed machine, if fails:

"failed to decrypt protected XML node DTS:Password...key not valid for use in specified state..."

Now this is definately to do with the custom log, as if I take it out & redeploy, I can run it on the deplyed server + also run it within a job. I have entered the custom log provider library (+ other required DLLs) in the GAC on the deployed machine, but I'm clearly missing something.

Any ideas pls? I'm really stuck.

Many thanks in advance,

Tamim.

Please search this forum...

You'll need to set the package protection level to "DontSaveSensitive" and then issue a password via the /SET command line switch. OR, you could try to promote to the server using SQL Server users/role level security, maybe....|||

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

|||

Tamim Sadikali wrote:

Thanks for the quick reply Phil, but that didn't work.

Is there anything extra I need to do/bear in mind with respect to the signed assemblies that comprise the custom log provider?

What do you mean that didn't work? That error message states that you are using indicates to me that the protection level is set to EncryptSensitiveWithUserKey, which means that ONLY the user who built the package can execute it.

Package Name Sorting

SISS packages do not sort by name in BIDS and they do not sort by name when deployed to a SSIS server if choosing to store in sql server. The packages do sort by name when stored in file system. We have more than 100 packages on the server and just could not keep up with the locations. The only thing I can do is to create many folders. But even folders have no way to be sorted. Do I miss something here?

Try this: http://solidqualitylearning.com/blogs/erik/archive/2005/08/08/1269.aspx

Or this: http://www.sqldbatips.com/showarticle.asp?ID=78 (Jasper has updated it to make it work in BIDS as well as SSMS)

-Jamie

|||

Jamie,

Thanks for your reply. I tried both solutions in BIDS. The first one works, but it seems too much work. The second one would be a good choice if I could make it work.

When I try to sort the packages in a project folder called 'Test Packages', I continue to get the follow error message in the output window "Access to the path ....Visual Studio 2005\Projects\Integration Services Project\Test Packages\Test Packages.dtproj.old' is denied.' I understand the program is trying to create a new sorted dtpproj file to replace the old one. Do you know what I need to do to make this work? Since the projected is in the source safe, does that make the difference?

Thanks.

Jane

|||

Sorry, I've never used it. Try asking Jasper - I'm sure he won't mind helping out.

-Jamie

|||It is probably a read-only file, and perhaps caused by some form of source control. Jasper makes the source code available, so feel free to come up with a better method.

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.sql

Package Fast in VS but slow when deployed to server

I have a package which completes in 3 minutes when ran from Visual Studio but when deployed to SSIS (same machine) it rans for more than 2 hours. What could be the reason for this?

Could you describe the package content and what does it do?|||

The package retrieves data from a source via a data flow task. The result is returned to the control flow as a recordset object. I have a ForEach container in which I am looping through the recordset. For each record, I am checking if corresponding records exists in another data source via SQL Task. If it exists I use another SQL Task to update it. If not, I add it via a third SQL task.

|||I never figured out what was causing this. Ended up redesigning the whole package and removing the foreach.