| Author |
Topic |
|
cmrhema
Starting Member
20 Posts |
 Posted - 2008-07-25 : 04:15:58
|
| Hi, I finished creating a package and it runs successfully, Now I want it to be run in SqlServerAgent so that it will run daily.I have it in Stored Packages MSDB, To run the jobschedule i went through the below websitehttp://www.mssqltips.com/tip.asp?tip=1180Can any one please let me know what do be done in the steps 3,4 and 8 in the above link.Or any other way that will enable to run in SqlServerAgentRegardscmrhema |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-07-25 : 05:47:42
|
| You can leave them empty.WebfredThere are 10 types of people in the world: Those who understand binary, and those who don't... |
 |
|
|
cmrhema
Starting Member
20 Posts |
Posted - 2008-07-25 : 06:50:27
|
| But Webfred, when I left that empty, what happend was the job was created successfully, and was executed successfully too. But in reality the package did not workWhy is that so.Regardscmrhema |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-25 : 07:46:59
|
quote:
Originally posted by cmrhema
But Webfred, when I left that empty, what happend was the job was created successfully, and was executed successfully too. But in reality the package did not workWhy is that so.Regardscmrhema
Are you sure package didnt work? or is it that package worked but didint give you desired result? |
|
|
|
cmrhema
Starting Member
20 Posts |
Posted - 2008-07-25 : 08:42:23
|
| I will explain exactly what did I doIn the BIDS created a packageIn this package we move the data to some other database.the package consists of a executesqltask1 which will execute a stored procedure which will return some 30 variables.All these are returned to the foreachloop container.This foreachloop container has a executesqltask2 inside it.All the variables are passed into the executesqltask2.This package when executed runs successfully.All data variables were passed and we got the desired results.Save the copy of the package in sqlserver in MSDB\Maintenenace PlanTo enable this to run daily, created a job agenthttp://www.mssqltips.com/tip.asp?tip=1180followed the above linkIn steps 3,4 and 8 were left emptyFurther the command line was not edited manually.Job was created successfully. It ran today at our scheduled time.Got the message in the history as"MessageExecuted as user: NT AUTHORITY\LOCAL SERVICE. The package executed successfully. The step succeeded."But did not give the desired results.So ran the package again, and we got the desired results.But the jobagent did not give the desired results. Why?regardscmrhema |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-25 : 11:00:12
|
| doesnt seem like package problem as message suggests it did run. What the stored procedure step doing? How is it generating value for variables? i guess you need make sure variables are getting values as expected. |
|
|
|
cmrhema
Starting Member
20 Posts |
Posted - 2008-07-25 : 11:30:32
|
| The first stored procedure will fetch the list of the tables where the data will be inserted. This does not have any input parameter, so i will store all the values in FullResultset. All these comes under executesqltask1.The variable of the above resultset is an object datatype named as "alltables"Now the foreachloop container , here i have chosen foreachadoenurmerator, selected the above variable alltables. Now to enable this to pass to another executesqltask inside the foreachloop, i have mapped adoobjectsource as "alltables".In foreachloop's variablemapping i gave a variable name "individualtable"Inside the foreachloop , i have executesqltask2. Here after giving the connection and setting type=directinput, gave the stored procedure name as exec sp_backupSplitData ?Here for this executesqltask2, there will not be any resultset, only i have to the input parameter mapping which i did as "individualtable".Thats it.So when i executed the package, the thirty table names were retrieved in the foreachloop container and all the datas were moved to the respective tables in proper order.What we do in the storedprocedure of executesqltask2 is we move the data to the table name which is sent as parameter.We keep only the last two days record in the main table the rest we move it accordingly.So everyday this package should work and move a days record to the respective tables.This was acheived successfully, in packagePreviously before attempting for package we used the stored procedure and created a asp.net program to call the procedure.Converted this program to batch file, and kept in scheduled tasks.While using the above it took 14 mins to transfer roughly 5 lakh recordsWhereas the package takes only 6 mns.Hence we opted for the package.Now I gave Save a copy of filepackage as in sqlserver in MSDB\Maintenenace PlanAnd the rest I think is in my previous question.*Sorry for the long explaination, but just cant know where i went wrongregardscmrhema |
|
|
|
sumit.microsofttech
Starting Member
6 Posts |
Posted - 2008-07-30 : 12:57:56
|
| When you run the package manually do you set any variables i.e. do you change the values of variables each time you run the package? Because, then you have to pass that value to that variable from SQL agent so that it executes with new value otherwise it will do the changes to old stuff.Have you tried logging the operations and see where it is getting wrong? |
|
|
|
|
scheduling the package in jobagent