I found the issue. The case statement result is of type DT_STR. Putting that result into a DT_WSTR could cause data loss thus the error.

I changed the data...

Leonardo Da Vinci said "simplicity is the ultimate sophistication." I did make things more complicated than they are, didn't I? Still have to learn to look at...

Thank you! I intentionally posted to see improvements. Improvements I got.

That is cool! Thanks!

No they are 2 separate accounts but both are local admins on the sql server box. Thanks

Thank you for the clarification.

We ended up adding the sql server agent service account as a valid CRM so that when xp_cmshell->dtexec->SSIS package the user is a valid CRM user...

Where would we that proxy account connect to?

The sproc alreadyhas EXECUTE AS svcAccount1.

The xp_cmdshell does not seem to have a run as or execute as -- so how do we...

Thanks!

Xp_cmdshell kicks off dtexec which runs the SSIS package.

The sproc executes as User1 but it appears that xp_cmdshell is hard wired to sql server agent service account so the SSIS...

Thank you for throwing some light. I was pulling my hair for an hour and so did our DBA.

Thanks! yes, I did have an expression with the folder variable. THe issue was the folder dynamic value was not passed to the SSIS package.

I found out how...

that dtexec works and passes XYZ to the package on my local sql server box. It does not pass XYZ on the sql server box -- must be some...

Your suggestion will work optimally. Unfortunately, I can't implement it as the service account does not have write permissions on Database2 tempdb and never will.

So I have...

I totally agreed with your point. I'd love to do the same thing with SSIS tasks but do not know how to pass a table value parameter in SSIS...

Update - I was able with script to convert the sqldatareader to a datatable and save that datatable into a user variable of type object.

Thanks!