Batch Job for SSIS and Stored Proc

Hi :

I have a SSIS package - A.dtsx and a stored proc -B.I want both of these to run once everyday at 1pm.Does not have to be simultaneously.Can someone please advise how I do it? I tried it both in Windows Task Schdueler and SQL Server management,but think i am missing something.please help.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use SQL Server agent to run it.  (many web tutorials on this)

Or, ifif you want to use windows scheduler, take the code in the box below and put in to a dtsPackage.vbs (make it a vbs file but name anything you want).  Then, under windows "Scheduled Tasks" just set a reference to this VBS file and schedule it.  

This second way uses the SSIS command line tool - dtexec.exe

'put this in to a *.vbs file
'Edit the path to your package
Set WshShell = WScript.CreateObject("WScript.Shell")

ReturnCode = WshShell.Run("dtexec.exe /FILE ""c:\...\PeoplesoftPull.dtsx"" /MAXCONCURRENT "" -1 "" /CHECKPOINTING OFF  /REPORTING EWCDI  ")

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Oh, as for the stored procedure, i would just put in to that SSIS package or create a new one.  

kmgingeeAuthor Commented:
Thanks carsRST!

I used your VB Script and it works for me :)

But how do I put my stored proc in my SSIS package?

I am so sorry.  I just now saw your comment.  Sometimes my emails get flooded.  

For a stored procedure, add a dataflow.  Within the dataflow, add your source (old db source, for example).

On your ole db source, go to connection manager, assign your connection, and use a "SQL Command" in the Data access mode.

WIthin the SQL Command text, paste in your SP (see below as an example).

After that, if you click on your columns and see data, you did it correctly.



exec [<<stored proc name>>] 1, 2, '01/01/1990', '3/31/2010'  

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.