Save a SSIS package to SQL Server or File System

printmedia
printmedia used Ask the Experts™
on
Hi all.

I'm working with our SQL Server 2008 and want to create a new SSIS package via the SQL Server Business Intelligence Development Studio, but I have a question as far as saving it to SQL Server or File System.

Which is the best practice? And why?

Also, when I create a new package and try to save it, I don't see the option to save it to SQL Server, it just asks me for a path. How can I save it to SQL Server?

Thank you in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database Analyst
Commented:
"How can I save it to SQL Server?"

You can use VS and create your SSIS packages as files then Import those into a SQL Server or for instance on a simple data Import/Export you can do that by using SQL SSMS under Database Tasks import/export wizard that will ask you at the end where you want to save it - File or Server?

As far as Best Practice...I always saved them as File so I can save them to my Source Safe repository and for versioning purpose.

Author

Commented:
Ok, here's what I plan to do and let me know if this is ok:

(1) I  create the SSIS package in BI and save it to "File" which will save it on a secured folder on my network that gets backed up nightly.

(2) Then I go to "Integrated Services" and Import it to: Stored Packages --> MSDB.

(3) Then I go to the Microsoft SQL Server Management Studio and create a Job to run my newly created SSIS package every day.

Is this a correct way of doing it? If I needed to make changes to the SSIS package would I have to open it again in BI save it and then "reimport" it in Integrated Services like I did in step (2)?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial