SQL 2005 SSIS: Runs in design fine, Not so good after Installation

Is your SQL Server installation 64 bit?

Ok.. Our SQL Server is 32 bit.. There is nothing running 64 bit in the whole company.

As for the DSN.. I double checked.. It was setup in the file rather then system.. So I added it to system too. But it didn't make a difference. Also Remember the exact same DSN connection is working fine for a 1 record pull in the first task..

So your right it Looks like the DSN is not connection by why would it connect for the 1 record pull and not for the other pull?

A couple of guesses:

1. Try setting DelayValidation = True
2. Maybe the account that the package is running under can't get to the file. Are you running this as a job?

Ok.. I have to restate the problem.. IT IS NOT WORKING FOR EITHER DSN CONNECTION. I thought the validation was in order and it is not, therefore it validated "Main" under the other DataFlow task first, and never even tried to validate the other one.. disable the "Main" task and the other one failed also..

Delay Validation didn't work.. But I saw something freaky with my connection string, and decided to install BIDS on the Server to figure out if the connection string wanted something different.. It Sure does..  And it corrupts in a way, I don't know if I can fix it on the server side.. (and I can't sit at the server all day doing my design work to create packages..)

Lets say my connection string has a UID: stupid  Password: STUPID... So I put those in the connection. on my desktop, my grayed out string shows  UID:,Password:******     My Server when opened blows up with errors shown below.. But if you build a new connection, It shows up as UID:STUPID,Password:


So one has blank UID & the password, the other has UID & a blank password.. And neither likes the other at all.

The errors are in a file attachment..

May be me, but I don't see the attachment.. So I am placing the errors here.

Error      1      Error loading DunBradtest.dtsx: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.        Y:\SSISProjects\VisionDataProjects\VisionDataProjects\DunBradtest.dtsx      1      1

(3 MORE ERRORS SIMILAR TO ERROR 1)      

Error      5      Validation error. Main Processing: DR for DunnBrad [31]: System.Data.Odbc.OdbcException: ERROR [08001] [Unify][UnifyClient ODBC Driver][UnifyClient][UnifyLNA][Unify][Unify DataServer ODBC Driver][Unify DataServer]The user name or password is incorrect. (1284)  ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed     at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)     at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)     at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection, OdbcConnectionString connectionOptions)     at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)     at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)     at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     at System.Data.Odbc.OdbcConnection.Open()     at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)     at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)     at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)      DunBradtest.dtsx      0      0      

I found after getting through the problem with my ODBC 3rd party server, that my SQL Server connection had similar issues!!! What gives? your suppose to do program design at the server?

Here is my work around..
   I made the connectors to my ODBC DB and SQL DB global..
   On the Server I created another project. With the same global connectors built with the same name.
   When I want to build an install the package, I go to the Server and in the BIDS bring up the server package. I import the package into it. Since the global connectors out pulled from outside the package they will then use the Server connectors and are happy.
   I build from the Server project.
   I import into SSIS the Server builds.    


If anyone has a better way to rid myself of this problem, rather then live with a workaround. I am all ears. But for now when I run out of SSIS it is successful.

Perhaps you did not see nmcdermaid comment the first time, so here it goes again:
"Are you running this as a job?"

In otehr words does it only fail when it runs as a job?

No, It would not run as a direct run from SSIS

Then I added BIDS to the server and when the package opened in BIDS It came up with  4 errors all having to do with the file connection and the password.

Now that I figured out how to create a new project and make the Connections for both the 3rd party DB and SQL Server global in both project have the same global connection but each projectcreated their connection from thier location. When I import a package from my desktop Bids project to my Server Bids project,my Bids program on the server does not have the errors. then Build the package from there and import that into SSIS.. My SSIS can run the project fine also..

I know I am hitting another error with the job scheduling.. Which I created a different thread for, and I think we are coming down to figuring that out.

I guess here I just feel I have solved the issue that it is something with the connections.. But since it is not only my 3rd party software, but SQL  and all tutorials say once you create a package you can just import it to any server.. I am hoping that someone can take the definate error, and tell me a better fix then my work-around 'cause I don't know the right way to fix it.

I will need to check on Monday. I believe it was encrypt with server or user key (what ever the default was..) but to try to get it into a scheduled job, I changed it to encrypt with password.. It will be interesting to see if that will solve this problem too..
I am excited to try it out, but am happy to wait for monday, and enjoy my weekend now.

Sorry it has been hetci at work, and I did want to write a more thourogh answer to this, but tomorrow is a holi, and I am already home, so I will have to write this as well as I can remember.

The 'encrypt with password' did not work for me, In solving the issue with the data connections being specific to the .. But I am getting the feeling that different things work at different sites.

What worked for me was 'Rely on server storage for encryption' which could only be set when you were in the process of saving it to the SQL Server. The feature is under the 'file' tab in the top bar, and is one down from the normal 'save as' function but something like 'save copy of 'your packagename' as.. Once you get into it it is pretty self explanatory. You want to save it to sql server - give the Server login info.. enter what you want the package called on the Server.. And the very last question is to change the protection level. It looks grayed out, but you can change it. You have to have the SSIS set up on your SQL Server for this because it will go in there under (I believe) 'MSDN' folder.

This protection level solved this issue and another one to get the protection level to submit it to a job Schedule Agent.

Yes protection level (not encryption level as I incorrectly stated) is the issue. All types of protection levels work in different ways. For example server encryption will work as long as you only run the package on the server. User encryption will only work if the pckage designer and the executor are the same.