May 30, 2008 at 3:52 am
I am deploying packages from various projects to SQL Server.
I have created folders for each subset of packages on my SQL Server and was wondering if it was possible to specify that packages get deployed to that folder rather than to the main directory.
Currently I am having to export the package to the required folder after deploying.
May 30, 2008 at 5:23 am
This was quite an interesting question, as I also found that my MSDB folder was getting really cluttered, so I played around a little.
I wanted to group my stored packages into projects...
Yes you can, but its not really neat.
Here is how I got it right:
a) In SSMS I connected to integration services
Under the Stored Packages, there are 2 defaults, File System and MSDB
b) If you right click on MSDB, you get to create new folders, so I created one
c) Here is the whole dinkum... Right click on the package and select export. Select the same server. Note that it now has a package path available.
HOWEVER, when you export, you now sit with 2 copies as opposed to one. So remember to delete from the root package folder.
Not very neat, but still workable
The downside is that when you run the package installation wizard, it doesnt give you the option to select a package path, so you always end up with your packages sitting under the root MSDB folder. Maybe something that the fellahs at Microsoft may want to look at, as it certainly will assist with housekeeping.
Trust this helps
~PD
May 30, 2008 at 7:17 am
Hi
Thanks for your post, that is exactly how I initially did it, until I found another way of moving the packages.
You can log in to the database engine and run a query on the sysdtspackages90 table in the msdb database.
Then, query the sysdtspackagefolders90 table and get the ID for the folder that you wish to move the folders to.
Update the folderId in the sysdtspackages90 table with the folder id of the folder you want to put the packages in and they will then appear in that folder.
I have created a stored procedure that runs after each deployment to make this change.
I was hoping there was a way of setting the path when deploying though!
This is the query that I run:
Update sysdtspackages90
Set folderId = (Select folderid From sysdtspackagefolders90 where foldername = 'MyNewFolder')
Where folderId = '00000000-0000-0000-0000-000000000000'
This moves the packages from the root to the specified folder (MyNewFolder).
June 1, 2008 at 10:55 pm
Awesome
Thanks for the tip
June 2, 2008 at 3:02 am
You're welcome!
This is really handy for me now....saves me from the tedious task of importing each individual package after each deployment!
June 3, 2008 at 6:39 am
Dipti,
Your method of using sysdtspackagefolders90 table is quite intriguing and new to me.
But I usually follow this procedure to deploy the package to a particular folder:
1) With the cursor being active on the package body you want to deploy,navigate to File -> Save Copy of As...
2) Enter the Server Name.
3) Click the icon beside Package Path, it then shows the folder structure from the root level i.e all folders in MSDB.
4) Select the folder you want to deploy the package to and enter the package name you want to name the package as and click ok.
5) Click the icon beside Protection Level and from the drop down list select 'Rely on server storage and roles for access control' and click ok. then ok.
Whenever you want to re-deploy the package follow the same procedure, it asks do you want to over-write the existing version. Click yes and ok.
This should work.
Regards,
Samata
June 3, 2008 at 9:57 am
Hi
Yes, that method works fine if you have just a few packages and can deploy them one at a time. But in my case, I have many small packages, and have to deploy them all at once. Not only that, I have to deploy them to different servers, so deploying each package like that individually would be a very tedious task.
Although, I have set up the structure like you said on my development server so I can keep redeploying when making changes to packages.
June 4, 2008 at 5:55 am
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply