June 6, 2008 at 8:57 am
We use SQL 2005 Enterprise 32 bit.
SSQL Service and BI Services are running on the same Windows Server.
SQL Server 2005 is operating in the mixed secutrity mode.
I converted my old SQL 2000 DTS packages into SSIS in VS.NET 2005/BIDS using SQL administrator account. The packages run fine in debug mode from VS.NET 2005/BIDS on my XP workstation.
When I try to deploy the packages to the server, the validation process informed me that OLE DB connections to the current instance of SQL Server are not valid (I am using a specific SQL 2005 account, and the password seems to be lost during deployment).
Questions:
1) How could I preserve the password in SQL 2005 connections during
SSIS project deployment?
2) What are main advantages and disadvantages of deployment into SQL Server (msdb database) versus the deployment into file system?
3) If I deploy SSIS packages into the file system on the server, would
I be able to invoke these SSIS packages programmatically from VS.NET
2005 application?
I would appreciate advice regarding any of the issues above.
June 6, 2008 at 9:18 am
1. Perhaps you're not executing the package on the server as the same account that created the package. Set the package Protection Level to something other than "DontSaveSensitive" or "EncryptSensitiveWithUserKey" before building and deploying the package. I've found that setting it to one of the "Encrypt..WithPassword" (sort of like an owner password in DTS) works. You could also try using package configurations to store the connection logins and passwords. See "protection levels for packages" in BOL.
2. Deploying to SQL Server allows you to use the msdb database roles to designate who can run a package. Deploying to the file system allows flexibility in moving packages to differenct servers/instances. As far as I can tell there's no performance difference.
Greg
June 6, 2008 at 1:06 pm
I really appreciate Greg's fast and helpful response.
Still, I have some further questions:
1) At this moment, I have just "Development" configuration of my Integration Services Project enabled in BIDS/VS 2005.
Is at least a trial deployment to the SQL Server possible without creating "Release" configuration?
2) Should I select the option "EncryptAllWithPassword" or "EncryptSensitiveWithPassword"?
Is the recommended option compatible with the deployment into the file system on the server?
3) (repeated from my 1st post) If I perform deployment into the file system on the server, would I still be able to invoke SSIS package programmatically from VC++.NET 2005 or VC# 2.0 application?
Could anybody clarify any of these issues to me?
Any help will be highly appreciated.
Thanks in advance.
Alex
June 6, 2008 at 1:26 pm
I prefer filesystem, although I haven't experienced the benefits of putting the package on the SQL Server itself. Yes, since it's filesystem, you can call them via any language that can execute a dos batch file.
You need 3 files of which one of them is your .dtsx. The second file would be your .dtsConfig file which contains the source & destination information and the path to store the log of the execution and any other info that needs to passed to your package...all of which are passed to the variables (say) in the package. The last file would be your dos batch/cmd file which would execute the SSIS package. At a minimum your batch file should have the following command.
Dtexec.exe /FILE "{fileshare}\{packagename}.dtsx" /CONFIGFILE "{fileshare}\{configFilename}.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
You just have to call this .cmd from your choice of prog language and if all your config settings are valid, you should be all set.
June 6, 2008 at 2:52 pm
Greg and AJ,
I am going to try your ideas and to post a brief report on the results on Monday.
Thanks and regards,
Alex
June 6, 2008 at 3:59 pm
AJ,
I have a couple of extra questions in regard to your deployment and programmatic invocation schema.
Are you talking only about server-side applications?
In my case, I need to run the application on the client side.
Could the latter be accomplished while SSIS packages are deployed to the SQL Server PC without granting users some kind of execute permissions on the shared folder on the SQL Server PC?
Could I deploy the packages to the shared location on some file server and invoke them from VC++.NET 2005 application running on Windows workstation?
Thanks in advance for your response.
Alex
June 6, 2008 at 8:37 pm
quickermind (6/6/2008)
AJ,I have a couple of extra questions in regard to your deployment and programmatic invocation schema.
Are you talking only about server-side applications?
In my case, I need to run the application on the client side.
Could the latter be accomplished while SSIS packages are deployed to the SQL Server PC without granting users some kind of execute permissions on the shared folder on the SQL Server PC?
Could I deploy the packages to the shared location on some file server and invoke them from VC++.NET 2005 application running on Windows workstation?
Thanks in advance for your response.
Alex
Alex,
Shared location on a fileserver should be ideal. I found this great link...it should have all that you need.
June 9, 2008 at 7:46 am
AJ,
This link is excellent.
Now I need to rethink and reestimate all production setup options.
Thanks and regards,
Alex
June 13, 2008 at 3:36 pm
Based on suggestions of Greg and AJ, I am trying to perform the SSIS Package Deployment into the file system on the server. I use the build/deployment option "EncryptSensitiveWithPassword", and I mark the following properties for encryption:
1) Connection String
2) User Name
3) Connection Password
4) Package Password
When I run the deployment manifest, I select the following options:
1) Deploy into File System and 2) Perform Validation
I still encounter 2 specific issues:
1) When I run the Deployment Manifest on the Server, the Deployment Wizard shows the read-only screen saying that all package passwords are invalid, and the Wizard prompts me to reenter all package passwords (I am not sure here; maybe it is the way everything is supposed to work)
2) After I have reentered all package passwords from the same Deployment Wizard screen on the server, the package validation process runs. The validation log on the screen shows that specifically the validation of all Data Flow Components in the packages fails because of the missing SQL 2005 password for the user “sa”. On the contrary, "Execute TSQL Task" steps are successfully validated.
Could anybody tell me what I am missing? Thanks a great lot in advance.
Alex
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply