| Author |
Topic |
|
clay_mckinney
Starting Member
14 Posts |
 Posted - 2010-10-26 : 14:50:18
|
| The company I'm working for (contracting) has a dedicated server from a hosting company. On the server, we have SQL 2005 and BIDS 2005. We also have .Net Framework 2 and 3.5 installed. I created an SSIS package and I run it every night as an Agent Job. Now, I'm writing a new SSIS package that is supposed to send a bunch of HTML formatted emails every night. Because the Send Mail Task only does plain text emails, I wrote a Script Task. Because this is BIDS 2005, the Script Task is written in VB. It does a database query, loops through the records, and sends a unique email for each record. I'm using a data adapter and a data view, because I'm used to that. BIDS made me insert a reference to System.Xml v. 2.0.0.0 in order to handle this. So, that's the set up. On the server, the package will run in BIDS and everything works great. But, when I try to run it as an Agent Job on the same server, it tell me that this version of SSIS doesn't support my package and an uplevel version is required. The error code is Code: 0xC0012024. I've googled around for a while, and haven't found much. One site indicated that, when SQL is installed, you have to check to install SSIS. I do have another SSIS package that's running fine, though. I'm wondering if the two can get out of synch in some way? Like if BIDS could get a patch or service pack or framework or something and the SQL engine be unaware of it? One person got this error and resolved it by reinstalling SQL. If I can prove that SQL needs to be reinstalled or updated or something, our host will do that for us. But if the problem is on my end, I'd like to resolve it. Do you have any ideas?- Clay McKinney |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-10-27 : 02:37:49
|
| Hm, I don't really know the answer to your question...but since the package is working in BIDS *on the same server* this sounds like a permission issue and not necessarily a version issue (both bids and the agent are running on the same server). Can you check that the user running the sql server agent / package has sufficient permissions?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
clay_mckinney
Starting Member
14 Posts |
Posted - 2010-10-27 : 08:23:28
|
| Thanks Lumbago. I went in and Disabled the Script Task which was the final step. Now the SQL Server Agent runs the SSIS Package. So, the only part it doesn't like is that Script Task. Are there separate permissions for that?- Clay McKinney |
|
|
|
clay_mckinney
Starting Member
14 Posts |
Posted - 2010-10-27 : 08:35:46
|
| Date 10/27/2010 7:34:04 AMLog Job History (UpdatesAndEmail)Step ID 1Server SV3411Job Name UpdatesAndEmailStep Name SSIS packageDuration 00:00:20Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: SV3411\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:34:04 AM Error: 2010-10-27 07:34:24.45 Code: 0xC0012024 Source: Send Emails Description: The task "Send Emails" cannot run on this edition of Integration Services. It requires a higher level edition. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:34:04 AM Finished: 7:34:24 AM Elapsed: 19.782 seconds. The package execution failed. The step failed.- Clay McKinney |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-10-27 : 08:54:10
|
| I'm really out on a limb here as this is quite far outside of my "comfort zone", but since it works in BIDS I still think there is a permission issue or something. I assume you are connecting to some kind of mail server in this script taks? Is this mail server in a domain? Does the local system account on SV3411 have permisssion to connect to it? Keep in mind that BIDS runs under the user account that you are logged on with, while the dtexec runs under the account of the sql server agent (which seems to be the local system account). The local system account wil by default not have any permissions on the network...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
clay_mckinney
Starting Member
14 Posts |
Posted - 2010-10-27 : 09:21:23
|
| The IP address for my SMTP is the same as the server. I have no idea how to set permissions for this. The SMTP will send mail from ASP.Net pages, so I know it works. I guess I need to know how to set it up for SQL Server.- Clay McKinney |
|
|
|
clay_mckinney
Starting Member
14 Posts |
Posted - 2010-10-27 : 23:53:44
|
| According to my web host tech support, where the error message says "It requires a higher level edition" it means that we have SQL Server for Workgroups and it needs SQL Server Standard Edition to run. I'm guessing the client won't want to be charged for that. Is there any other way to schedule a program to run on a schedule, that can loop through some records and send some emails? Doesn't sound that complex, and yet I don't know...- Clay McKinney |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-10-28 : 03:25:04
|
| Oh...that explains it then! I have done something like this very a long time ago and I managed to find the topic about how it was don. It was way back in sql 2000 though but I actually think it would still work: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36293It's worth a shot...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
SQL Agent Job says SSIS needs to be Uplevel