Package with File System Task doesn't work without sensitive data with user keys

This problem is a bit weird but I'm just wondering if anybody else experienced this.

I have a package that has file system tasks (copying dtsx files actually). Basically the package copies other packages to a pre-defined destination. Thing is, it only works if one of the packages it is configured to copy has some sort of sensitive data (e.g., a connectionstring with a password), otherwise it reports a success message on execution but doesn't actually do anything. I've checked the forcedexecutionresult and it is set to None for that matter.

Just wondering if anybody else experienced this problem and of course if there's a way to solve it.

Thanks.Could the security permissions be different on your packages that are failing to be moved from those that are succeeding?|||No, they're all set to EncryptSensitiveWithUserKey.

However, all of the packages involved have their sensitive information set to obtaining them from Indirect Configuration Files. The only way around that I found so far was to put in a dummy connection object, set up an XML file package configuration for that dummy, and deploy it that way.

I'm suspecting that the user key isn't generated at all when all the packages get their configs from Indirect Configuration Files. For some reason, DontSaveSensitive doesn't work either. My original package insists on the presence of a key. Might have something to do with file read/write/modify permissions that come with the key?

I still want to do away with the dummy though.

Ownership issue problems..... (I think)

I created a database on my laptop, and because I was hoping to keep
the data, I tried detaching it and copying the files to my server and
attaching the database to the server.

I quickly noticed that some of my SPs didn't have rights that I was
expecting them to have. One SP executes a line of dynamic SQL that
updates a record and I get errors saying it doesn't have SELECT
permission or UPDATE permission.

So... I removed the database from the server and scripted the DB.

I logged in to Query Analyzer as 'sa' and ran the create scripts.
The DB lists 'sa' as the owner, and everything in the database lists
'dbo' as the owner, but running those SPs still gives me the same
errors.

Any clues? What can I try to further diagnose this problem?Dynamic SQL breaks the ownership chain. Consequently, users need
permissions on the objects directly referenced in the dynamic SQL statement.

You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
SQL Considerations.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Chris" <cmay@.walshgroup.com> wrote in message
news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> I created a database on my laptop, and because I was hoping to keep
> the data, I tried detaching it and copying the files to my server and
> attaching the database to the server.
> I quickly noticed that some of my SPs didn't have rights that I was
> expecting them to have. One SP executes a line of dynamic SQL that
> updates a record and I get errors saying it doesn't have SELECT
> permission or UPDATE permission.
> So... I removed the database from the server and scripted the DB.
> I logged in to Query Analyzer as 'sa' and ran the create scripts.
> The DB lists 'sa' as the owner, and everything in the database lists
> 'dbo' as the owner, but running those SPs still gives me the same
> errors.
> Any clues? What can I try to further diagnose this problem?|||Thanks Dan!

This really helps.

The article you linked too is really good also, thanks.

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<X5aQb.20884$q4.11340@.newsread3.news.atl.earthlink. net>...
> Dynamic SQL breaks the ownership chain. Consequently, users need
> permissions on the objects directly referenced in the dynamic SQL statement.
> You might peruse http://www.sommarskog.se/dynamic_sql.html for other dynamic
> SQL Considerations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris" <cmay@.walshgroup.com> wrote in message
> news:44f6e099.0401222324.352e9d5@.posting.google.co m...
> > I created a database on my laptop, and because I was hoping to keep
> > the data, I tried detaching it and copying the files to my server and
> > attaching the database to the server.
> > I quickly noticed that some of my SPs didn't have rights that I was
> > expecting them to have. One SP executes a line of dynamic SQL that
> > updates a record and I get errors saying it doesn't have SELECT
> > permission or UPDATE permission.
> > So... I removed the database from the server and scripted the DB.
> > I logged in to Query Analyzer as 'sa' and ran the create scripts.
> > The DB lists 'sa' as the owner, and everything in the database lists
> > 'dbo' as the owner, but running those SPs still gives me the same
> > errors.
> > Any clues? What can I try to further diagnose this problem?