Hi,
I used to write DTS Scripts in SQL Server 2000 and schedule them as jobs with out problem.
This was normally done within SQL Server its self.
Now that I've moved to using SQL Server 2005 I've been learning how to use SSIS.
I've successfully developed a package and managed to create a .dtsx file. Now I have 2 large books on the subject of SSIS but none seem to go into any detail on what to do next.
So here’s my newbie question (I apologise if I sound dumb!):
I don't want to run my package manually as the books keep telling me how to do.
I need to have my package added into SQL Server 2005 somehow and then schedule it as a reoccurring job.
Can anyone point me in the right direction?
Thanks
Matt.
Have you tried this -
Deploying Integration Services Packages
(http://msdn2.microsoft.com/en-us/library/0f5fc7be-e37e-4ecd-ba99-697c8ae3436f.aspx)
In summary, there is the deployment "utility", which can be produced via the SSIS project in BIDS, or you can import packages to a server from within SQL Server Management Studio. For my money the best option is to just put the dtsx files on the server somewhere as files. How you do that is up to you, a manual copy for example, or perhaps get a bit clever and use an MSI.
To schedule the package, you can create a SQL Server Agent job in much the same way as before. Use SQL Server Management Studio, and create the job or write a T-SQL script. You can of course build the job in the tool and then script it too.
On point though is I much prefer to use a CmdExec job step type, and just call dtexec.exe with parameters over using the built in SSIS job step, as you can get better output from the former. So really you are just scheduling an exe.
Use DTExecUI.exe to help build the command line, but of course use DtExec.exe in the job itself.
This thread may also help -
Re: using sql server agent stored procedures to execute a package - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=646512&SiteID=1)
Thanks,
I've read through the articles on those links with the most helpful being this package installation example :
http://msdn2.microsoft.com/en-us/library/ms365344.aspx
I also think using the command line method that you mentioned sounds like a very good idea and the whole process seems simple.
So I've gone to use the SQL Server Agent set up and schedule a job and low and behold that has completely changed from the SQL Server 2000 version too!
However it does look good but means I still need some more reading yet before I can add my job in.
Thanks very much for your help.
Matt.
