problem in transporting SSIS Packages from the local system to development

Question

 

Posts 7

Dear all I have developed some packages (around 40) on my local system. now i m trying to move the integration services project on the production server. when i double click on the intergration services from the local drive of the server all the packages are up(works fine till here) now i open any of the package-->this is what happens Prompt1)-->TITLE: Microsoft Visual Studio ------------------------------ There were errors while the package was being loaded. The package might be corrupted. See the Error List for details. ------------------------------ BUTTONS: OK ------------------------------ i press ok and then the prompt comes Prompt2-->There were build errors.Do you want to continue with the last build and continue..yes..no.. i click yes ..and then error comes   prompt3--> Error loading RTS-IMRB-DISTRIBUTION.dtsx: The connection "Excel Connection Manager" is not found. This error is thrown by Connections collection when the specific connection element is not found.  and finally the prompt for the OLE DB error prompt 4-->[OLE DB Destination [14]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "NDELNTX46.IMRB RTS.siddharth" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.       Possible work around suggested to me was that in the base system(read local) before exporting go to package properties and the security and set the security as "Encrypt all with password" and also put some Password which i did and it worked for one. other thing i thought off was creating the configuration file but first i thought of fully running one complete package on the production and then think of the configuration file. so i went inside the invidual excel connection and pointed it to the correct new file location and also edited the OLE DB database connection and put my user name and password and checked save password and trying to run the entire thing. but still the same problem.. one more thing i observed was that my OLE DB was going off everytime i am running the package.and i have to enter my password again...so othe save password i guess is not working   I know its a little long post..but i wanted to explain the entire process and problem to all with description. all suggestions are welcome   Thanks in advance cheers & Happy new year sid

Answer 1

 

Have you considered using an XML configuration file ?

 

If not , you should . It will ease you SSIS deployemnts and make your packages portable .

 

You should also store you configuration files in a standard locaion .

 

Look here for more info http://msdn2.microsoft.com/en-us/library/ms141682.aspx

Answer 2

U have to Manually enter password in config file.It will not take automatically.

 

Try this.

Answer 3

You can use package configuration to make your package 'portable' across servers.

 

This is the BOL topic, nut there are tons of extra info in this forum and on the web

http://technet.microsoft.com/en-us/library/ms141682.aspx

 

This KB article covers some common issues when running packages via SQL Server agent and some considerations when deploying packages:

 

http://support.microsoft.com/kb/918760