I previously wrote article SQL SERVER – Import CSV File into Database Table Using SSIS. I was asked following question by reader that how to run the same SSIS package from command prompt. In response to the same I have written article SQL SERVER – Running SSIS Package From Command Line. Within few minutes of the blog post, I received email from another blog reader asking if this can be scheduled in SQL Server Agent Job.
Absolutely, SSIS package can be scheduled in SQL Agent Jobs. Here is quick note on how one can do the same.
First you can create new job from SQL Server Agent Menu.
Create New Step.
Select Type as SQL Server Integration Services Packages. Select Package Source as file system and give package path.
Now click on OK, which will bring you at following screen.
On next screen you can select schedule and configure desired schedule.
You can notice this is very easy process. Let me know if you have any further questions.
Reference : Pinal Dave (https://blog.sqlauthority.com)
109 Comments. Leave new
Hi,
I should say you saved my day with this solution. Thanks alot!!
Thanks S.
Im getting this error. SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, see “Surface Area Configuration” in SQL Server Books Online.
I already did these:
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1
GO
RECONFIGURE;
GO
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE;
Nothing happens.
Hi Pinal,
is there a way to schedule SSIS package in SQL agent to run between 12th working day of each month to 20th working day (working day I mean include only Monday to Friday ) not 12th calendar day….
There is no direct way to do it t… I think you need to check the day in first step ( usually we do it by using our dimension table which have all the information about day and working day). And in second step check if first step return true run your ssis job using stored procedure ( search help on how to execute ssis package using stored procedure).
And in third and last step execute stored procedure in your SQL agent job and schedule it daily…
Job will execute the stored procedure daily and stored procedure check the criteria and execute ssis..
after scheduling i executed job but,getting error saying that”The job failed. The Job was invoked by User . The last step to run was step 1 (ssispkg).”
Above is very generic so you need to perform logging to get exact error.
That is the final outcome of job… For detailed information please check the history of each and every step of your agent job.
That is the final outcome of SQL agent Jon if it fails. Check job history of each step which will give you detailed information about error.
plz help with the solution
Hi Pinal Dave,
Environment SQL Server2012. I have added DTSX package in the SSISDB – Integration Services Catalog. I could able to execute the dtsx from this location. I need to know, how to schedule a job and execute them using sql agent. Hoping to get a solution from you.
Regards
Bala
Hi Pinal,
I have one ssis package which is running fine using SSDT 2015 but when i tried to run that package through sql job its failing saying some dts_e_inputmethods failed. derived column task failed. i am performing calculation in derived column and redirecting error to different destination. It works fine in SSDT but not in JOB. Help me with this.
Hello Pinal,
I want to push the last date attendance excel report daily after 12 AM from attendance website to our SQL Server automatically. How can I upload the last date attendance excel report to the SQL server and from here to the client SQL server automatically? Can you please guide me how can I do it. It is my responsible to it now.
Hello Pinal,
I have a olap tabular cube that is failing in refresh data, because of following error.
Executed as user: NT AUTHORITYLOCAL SERVICE. Microsoft.AnalysisServices.Xmla.XmlaException: An unexpected error occurred (file ‘pffilehash.cpp’, line 3261, function ‘PFFileHashTable::ExpandAux’).The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The current operation was cancelled because another operation in the transaction failed.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties) at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag). The step failed.
Actually I have a sql server agent job that refreshing the cube daily with Full Refresh and IN-Memory configuration. Previously it was failing because of the memory not sufficient on the server. Its eating up 100% memory and failed. I have increased memory and again tried to refresh and Its failed again due to memory not available. Again tried to allocate some memory and trying to refresh the cube data and now this time we got different error as specified above.
Please help to resolve this error.
Thanks
Kiran Kharat
Hi
I have SSIS package 2012, i want to schedule the Job in Sql server 2008.Is it possible to do?. If yes, Please share the steps.
HI
Is it possible to schedule the ssis package 2012 Job in sql server 2008?. If yes, please share me the steps