Folks...
I have created a DTSX package for a requirement which fetched data from MS access and store it in SQL Server.For business calculations there we some Views required. The final stored procedure will insert data into a table which will be utilized by a ERP system. The insert query was a bit complex like it called fields in a View which was created from a view (can call it a nested view?) & so on... The execution time was more than 30 mins :( So we need to optimize things for better performance....
now my next attempt will be to try it out with temp tables. [drafting post...]
Yes... In place of the views I used two temporary tables and one view i kept it as it is because, It was only displaying distinct values from a table.
This time to complete execution the Stored procedure took only 34.031 seconds...
Wow... that's a huge difference.... I believe using Temp tables optimized the stored procedure than views. The point to be noted here is that the decision to go for View & Temporary tables is based on the scenario... In my case Temp tables did the trick :)
Best Regards
Fauzi
Showing posts with label Business Intelligence. Show all posts
Showing posts with label Business Intelligence. Show all posts
Friday, July 31, 2009
Sunday, September 7, 2008
Solution for SSIS job : The package execution failed. The step failed.
Hi...
I created a dtsx package which will fetch data from Excel file and insert them into respective SQL Server table. When i run the package from SQL Server Business Intelligence Development studio it works fine. When i run package from Command prompt also it works fine. But when we create it as a SQL Server Agent Job, It says the following error.
The package execution failed. The step failed.
I found this article from Microsoft Support team for all scenarios while execution of Job failed.
http://support.microsoft.com/kb/918760
It was clear, in my case it was permission issue, So i kept looking for blogs on such case. Managed to find this blog which helped me to solve the issue.
Following are two points which i believe, did the trick for me...
1. Under SQL Server Configuration Manager:
* Right click SQL Server Agent(MSSQLSERVER)
* Click Properties
* Select radio button this Account, Gave the User Name & Password (the user name with which i have created the .dtsx package )
2. In Computer Management(Right click on my computer & Click Manage)
* Under Local Users and Groups
* Under Groups
* Right Click SQLSERVER2005SQLAGENTUSER$YourServerName$MSSQLSERVER
* In Properties add the user name with which i have created the .dtsx package
These steps solved my problem, Hope this should be helpful :)
Regards
Mohammed Fauzi
I created a dtsx package which will fetch data from Excel file and insert them into respective SQL Server table. When i run the package from SQL Server Business Intelligence Development studio it works fine. When i run package from Command prompt also it works fine. But when we create it as a SQL Server Agent Job, It says the following error.
The package execution failed. The step failed.
I found this article from Microsoft Support team for all scenarios while execution of Job failed.
http://support.microsoft.com/kb/918760
It was clear, in my case it was permission issue, So i kept looking for blogs on such case. Managed to find this blog which helped me to solve the issue.
Following are two points which i believe, did the trick for me...
1. Under SQL Server Configuration Manager:
* Right click SQL Server Agent(MSSQLSERVER)
* Click Properties
* Select radio button this Account, Gave the User Name & Password (the user name with which i have created the .dtsx package )
2. In Computer Management(Right click on my computer & Click Manage)
* Under Local Users and Groups
* Under Groups
* Right Click SQLSERVER2005SQLAGENTUSER$YourServerName$MSSQLSERVER
* In Properties add the user name with which i have created the .dtsx package
These steps solved my problem, Hope this should be helpful :)
Regards
Mohammed Fauzi
Subscribe to:
Posts (Atom)