April 16, 2008 at 6:42 am
I have scheduled a SSIS package under a SQL Job.
This Job takes around 90-95 secs to initiate the SSIS package. The execution time of the Package within this job is good but the issue is w.r.t the time taken by the SQL Job to initiate the package.
Has anyone else come across similar problem
Regards,
Amit
April 16, 2008 at 7:07 am
I'm not sure what you mean by "initiate"?
When I look in the history of my jobs that run SSIS packages it just gives me the time it took to run a package - are you seeing a step that says it is initiating the package?
April 16, 2008 at 8:37 am
I meant the correct time when the Job actually starts running the SSIS Package.
My Job History showed me total duration of 2 min 55 secs.
But I think the actual time taken by SSIS was just 1 min 23 secs. I knew this because I checked my event log and there were two entries under the Source "SQLISPackage". These entries describe the Start and End of The Package. And the difference of time between these entries was only 1 min 23 secs. I hope SSIS does not take the first 1 min 32 secs to just Validate the entire package.
You can even check your event log after your Job completed succesfully. Compare the Event Time Stamps against the actual Job schedule time. Or if you are running the job manually then take a note of the System Time before you start it.
April 16, 2008 at 8:46 am
In my case the times match in eventviewer and in SQL Server.
Check the job history and see when the job started (according to SQL Server). Also, expand the job in job history and check the SSIS step. At the bottom you should see something like:
Message
...The package execution returned DTSER_SUCCESS (0). Started: 00:30:00 Finished: 01:37:24 Elapsed: 4044.23 seconds. The package executed successfully. The step succeeded.
See what it says for the Started time and what that's like compared to the actual job start time. The job start time is at the top of the info window when viewing the job history.
April 16, 2008 at 9:02 am
I checked the Job Start Time as suggested by you. I compared this Start time stamp against the event log Time Stamp for the entry "SQLISPackage" (which has the Description: Package "MYPackage" has Started").
Both the Times are different.
Start Time of the Job was: 4/16/2008 7:38:58 PM
Event Time Stamp of "SQLISPackage": 4/16/2008 7:40:29 PM
Do you see any event log entry with Source: "SQLISPackage" on your box? This entry is written by the SSIS Package and not by the SQL Job. So you can actually find the when the SSIS package actually started.
April 16, 2008 at 9:07 am
On my box the Event Time for the package starting is just a couple of seconds out from my job start time.
Did you check the package start time that you see in the job history?
Out of curiousity....check the getdate() time you get in SQL Server compared with the server time. I can't imagine they'd be different but it might be worth checking anyway.
April 16, 2008 at 9:41 am
I checked the whole stuff on a different box which had more data.
Sys Time and SQL Server Time are in Sync.
I re checked the job History. Package executed succesfully and my files were produced. The DTS start time as per the history was same as the Job start time. But the issue is still the same.
Also, there was an error. Below is the message from History Page.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message
Executed as user: INBLRNBS0001\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:38:58 PM Error: 2008-04-16 19:38:58.85 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 7:38:58 PM Finished: 7:47:33 PM Elapsed: 515.266 seconds. The package executed successfully. The step succeeded.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In the above, Start Time for the Job is 7:38:58 PM where as Event time Stamp for "SQLISPackage" was at 7:40:29 PM.
Could the above error be the cause for time delay???
Thanks,
Amit
April 16, 2008 at 10:17 am
Amit Kumar Sunkuru (4/16/2008)
I checked the whole stuff on a different box which had more data.Sys Time and SQL Server Time are in Sync.
I re checked the job History. Package executed succesfully and my files were produced. The DTS start time as per the history was same as the Job start time. But the issue is still the same.
Also, there was an error. Below is the message from History Page.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message
Executed as user: INBLRNBS0001\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:38:58 PM Error: 2008-04-16 19:38:58.85 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 7:38:58 PM Finished: 7:47:33 PM Elapsed: 515.266 seconds. The package executed successfully. The step succeeded.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In the above, Start Time for the Job is 7:38:58 PM where as Event time Stamp for "SQLISPackage" was at 7:40:29 PM.
Could the above error be the cause for time delay???
Thanks,
Amit
As per above the start time of the job is exactly the same as the start time of the SSIS package. Or at least the package started at 7:38:58 PM, which is the same time as the job started according to you. My guess is that whatever is recorded in the Event Logs is wrong.
April 16, 2008 at 11:38 pm
To check that I put a Script Task as the first shape in the Control Flow. This task writes an EventLog Entry.
I executed the modified package by manually running it under the Job. I checked the entry in the event log which occurred only after 1 min 32 secs after I started the Job. This means that SSIS Start time is different then the Job Start Time. I think Message in the Job history is incorrect
I think there could be some other issue. May be I am missing some settings or may have modified some properties accidentally.
April 18, 2008 at 11:40 am
I have the same issue.
it takes more or less almost exactly 90 seconds for the SSIS to start work after the job starts, i checked that manually where my first task(clearing a table) cleared the table after 90 seconds from when the job started. We accepted it as the time needed by SSIS. we don't have huge data so it is not a big deal, as after the 90 seconds, the SSIS typically needs 30-60 seconds.
I haven't looked for a solution but I wanted to say that I have the same problem which doesn't bother me at the moment.
April 18, 2008 at 11:53 am
Amit Kumar Sunkuru and schumi
How are your SSIS packages saved/deployed? What I am looking for are what parameters you used when you saved/deployed the packages.
😎
April 18, 2008 at 12:03 pm
Lynn, can you be more specific? which parameters you are looking for as there are many different settings everywhere.
April 18, 2008 at 12:30 pm
Not sure off hand. Just wondering how you saved/deployed the SSIS packages that have delays in execution. You say I may have a gut feeling, but need more information to confirm.
We have a lot of SSIS packages that run under scheduled jobs, and they don't suffer from the delays you describe.
😎
April 18, 2008 at 12:46 pm
well, i work in BIDS and don't use a password for anything (i mean no encryption thing). I "build" the package then (there are many ways i do this, but one of them)go to integration services, then add a package there. Then create a job in sql, create a step, add the package. I use windows authentication there... As the other person said, it feels there is validation or loading going on. I don't see the server, but we have DBAs. I didn't make a big deal out of it so we didn't do further inspection. Maybe someone out there had to solve this problem and can share with us the solution.
thanks
April 21, 2008 at 7:02 am
Lynn,
I have created the Manifest and have deployed it (File System) on a different Box. Next, I have created a scheduled Job on that box's SQL Server Agent and included a Step to call the Package from the Installation Location. The job runs under a newly created user which is a sysadmin. This user is different from the Logged in User that actually installed the Manifest and created the Job. But yes the Logged in User is the Administrator of that box.
Regards,
Amit
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply