Storing SSIS packages to MSDB

Question

Post reply

Storing SSIS packages to MSDB

day-476284

SSCommitted

Points: 1844
More actions
August 26, 2008 at 10:17 am

#197571

Hi All,
Anyone knows how to update an SSIS package in MSDB other than droping the whole package and copying the newest version from the file location by using DTUTIL?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

day-476284 SSCommitted Points: 1844 More actions · Answer 1

Hi,

I found the ealier discussion

http://www.sqlservercentral.com/Forums/Topic233916-148-1.aspx

Basically the only version control option is:

1.Using SSMS Integration Services, export the package to file system.

2.Using BIDS, edit the package and save to the file location

3.Using SSMS, drop the package from SQL Server Agent

4.Using SSMS Integration Services, drop the package

5.Using DTUTIL copy the package back to msdb

6.Using SSMS re-create the job and the schedule.

I found the version controling is such a hassle just to be able to do minor changes if you want to run the package as scheduled job in SQL Server Agent.

I guess, this is the only way to manage it?

Thanks,

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

As far as I can tell, the best way is to keep the latest version in an integrated source control and do everything from BIDS.

You could just store the packages on the file system and not register them on the SSIS Server at all.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

This is one of the reasons we decided not to store packages in msdb anymore like we did with DTS packages.

day, I don't think you'd have to recreate the job. The job step should use the latest version of the package anyway.

Greg

day-476284 SSCommitted Points: 1844 More actions · Answer 4

Hi Greg,

Previously, I couldn't manage to run SSIS package from a file location as a SQL Server Agent Job. That's the reason I need to deploy my package to msdb all the time.

Do you know any good tutorial which describes the steps for "How-to-run a SQL Agent Job from a file"?

Thank you

/Day

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

http://technet.microsoft.com/en-us/library/ms139805(SQL.90).aspx

http://www.mssqltips.com/tip.asp?tip=1180

You could also describe the problem you had previously and maybe someone will be able to help.

Greg

day-476284 SSCommitted Points: 1844 More actions · Answer 6

Hi Greg,

Thanks for your help. I really appreciate that.

I followed the steps from http://www.mssqltips.com/tip.asp?tip=1180 to run my package from a file system in SQL Server Agent. I managed to get that working this time.

The problem was I didn't store my package into SSIS Package Store before. I tired to run it from the file system directly. The message was "xml wasn't required format".

Now, it is working.

Thanks again.

day-476284 SSCommitted Points: 1844 More actions · Answer 7

I found that it is the same deal when it comes to version controlling.

In order to run the package in SQL Server Agent, we have to store our package either in [msdb] or [SSIS Package Store]. Then after, if we need to edit the package, we need to re-import the package into [Stored Packages] in Integration Services. Otherwise it doesn't propagate the changes from the real file source.