August 4, 2008 at 10:49 am
I've got a scheduled (twice a month) job using the SQL Agent that looks for an SSIS package in MSDB by name. I do my development on the package in it's FILE form, then every 2 weeks, commit it to MSDB.
My complete "push dev to prod" routine could boil down to...
1) Stash production.dtsx to production_YYYYMMDD.dtsx in the ARCHIVE folder
2) Save development.dtsx as production.dtsx
3) open the new production.dtsx and CHANGE THE DB CONNECTION OBJECT to point to MD_Dw
4) save the new production.dtsx to the MSDB database (push it live for the scheduler to see)
First, the File System Task doesn't seem to have an obvious facility for renaming a file using today's date... _YYYYMMDD.dtsx
Second, I need to update a database connection object. In dev, I'd like it to always point to "devDBName", but before I commit it to MSDB, I'd like to redirect it to "DBName".
Lastly, the actual save to MSDB from the file object, so that the SQL Agent sees the updated SSIS package.
... is there a way to automate all 3 of these tasks? Does someone have a better paradigm altogether that might suit my needs?
Thanks!
Greg
August 5, 2008 at 6:47 am
It seems a bit odd to assume your changes should be pushed out on a regular schedule. Are you always done with any changes every two weeks?
Skipping that, the three items you want to accomplish are not that hard.
1) Renaming a file with a date extension will work best for you with a file copy. You can use variables for the from and to file locations. Use an expression variable for the To location and concatenate in the date information. If you have trouble with the expression, let me know and I will open the editor and create one for you.
2) You should search around a bit on this site and others for package configurations. This is the most stable approach and the MS recommended one for having connection strings appropriate for an environment.
3) There is a DTUTIL command line utility for sending a file to the MSDB database. If you are uploading to the local SSIS server, the command will look like:
[font="Courier New"]DTUTIL /FILE "-File System Location-" /COPY SQL;"-MSDB Folder--SSIS Package Name-"[/font]
August 6, 2008 at 1:42 pm
this is exactly what I needed. Already got the package configuration hammered out... Thanks for the direction.
August 6, 2008 at 3:21 pm
Something I didn't consider... I have a report solution in SSRS with about 30 reports. They all use the same DBConnect object... Is there an equivalent "package configuration" concept in the Reporting Services that could use the same table ([SSIS Configurations]) so set the Connection back and forth between DEV and PROD?
BTW, this helped me out on the package config stuff... http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html
Thanks again.
August 7, 2008 at 5:08 am
If your reports use common connection files, switching from dev to prod is easy. In the project properties, there is an option to overwrite connections. Set the option to false and when you deploy, it will not re-deploy connections if they are already there.
August 7, 2008 at 11:51 am
Thanks Michael.
I would like to take you up on describing or illustrating your paradigm for assigning a date string to a variable, then using that in a File Copy operation. I'm hanging up. I've got a variable assigned with yyyymmdd_hhmmss... How do I get that into the destination path for the copy object though?
I tried using an expression in the File Copy object that sets the "destination" attribute to
"C:\mypath\myfilename_" + @[User::myUserVar] + ".dtsx"
...but it keeps resolving to "C:\mypath\myfilename_"".dtsx
?? I guess I've got no value for the variable? I'm probably taking a circuitous route by assigning it to the output of a SQL sproc "usp_CurDateTimeString"
The syntax I'm using in that assignment is:
EXEC usp_CurDateTimeString ? OUT
and I've assigned the User::myvar on the parameters tab.
Thanks
August 7, 2008 at 1:27 pm
Nix it. Got that sorted. Working through the report deploy.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply