Package fails when I use ODBC connection (Fails on SQL Server Agent, OK in Visual Studio)

I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.

Configuration:

SQL Server Agent on a 32Bit server.

The ODBC connection configuration in available on System DSN on this server.

The user of Server Agent have full access (Admin).

Connect Manager Provider: ".Net Providers\Odbc Data Provider"

SQL Server version: 9.0.3042

Error Message:

Executed as user: TEKCON\tcadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.

I created a .bat file with this instruction and It's run well:

dtexec /f "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Integration Services Project\testcom.dtsx"
pause

Why it's not running with SQL Server Agent?

Hi Daniel,

I've not seen this with ODBC sources before but I don't use a lot of ODBC sources. Based on your description of the problem I suspect the privileges on the SQL Agent account. If possible, log into an SSIS development workstation with the SQL Agent account credentials and try the package in Visual Studio. If it executes, then my suspicion is incorrect.

Hope This Helps,
Andy

|||

Thanks Andy,

I tried your suggestion: I logged into the SSIS development workstation with the same user than the SQL Server Agent. It's running with Visual Studio but fail in the SQL Server Agent.

Bye!

Daniel|||What type of step are you using in SQl Server gant job? Did you try using CmdExec type with the same command line you indicated in your previous post?|||

Hi Daniel,

You may want to try the following test: Change the package ProtectionLevel property to something containing the word "Password" (EncryptAllWithPassword, EncryptSensitiveWithPassword) and supply a nice strong password in the PackagePassword property. Save the changes.

Execute the package using the DTExecUI utility. You will have to supply the password to do so. If this succeeds, schedule the package execution using a SQL Server Integration Services Package job step type. Again, you wll need to supply the package password.

If this does not work, please copy the error and paste it in your response.

Hope this helps,
Andy

Overflow or underflow in the arithmetric operation

Hello,
Has anyone encounter this error message when they try to preview the report.
I create a simple report with an external ODBC data source. The report has
a list of name (string) and values (double).
When I try to preview it keep on giving this error. I has no clue what is
causing this. I'ved try this ODBC on other application they all work fines.
It is just this new MSRS I am having trouble with.
I am using VS 2003.Net with MSRS service patch 1.
-rsUsually this is because on an expression being used that is doing math. If
you use an iif function you need to be careful because every part of it
(both true and false) get evaluated which can cause overflow, divide by 0
type of errors.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rs90144" <rs90144@.hotmail.com> wrote in message
news:%23n6J0kM$EHA.600@.TK2MSFTNGP09.phx.gbl...
> Hello,
> Has anyone encounter this error message when they try to preview the
report.
> I create a simple report with an external ODBC data source. The report
has
> a list of name (string) and values (double).
> When I try to preview it keep on giving this error. I has no clue what is
> causing this. I'ved try this ODBC on other application they all work
fines.
> It is just this new MSRS I am having trouble with.
> I am using VS 2003.Net with MSRS service patch 1.
> -rs
>|||The message sound like some kind of math was done. But in the report there
are
no IIF funcatons or any other funcation, just a simple list of value from
the ODBC driver. That's why it is so strange.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:#VtBMFN$EHA.2804@.TK2MSFTNGP15.phx.gbl...
> Usually this is because on an expression being used that is doing math. If
> you use an iif function you need to be careful because every part of it
> (both true and false) get evaluated which can cause overflow, divide by 0
> type of errors.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rs90144" <rs90144@.hotmail.com> wrote in message
> news:%23n6J0kM$EHA.600@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > Has anyone encounter this error message when they try to preview the
> report.
> > I create a simple report with an external ODBC data source. The report
> has
> > a list of name (string) and values (double).
> > When I try to preview it keep on giving this error. I has no clue what
is
> > causing this. I'ved try this ODBC on other application they all work
> fines.
> > It is just this new MSRS I am having trouble with.
> > I am using VS 2003.Net with MSRS service patch 1.
> >
> > -rs
> >
> >
>|||What is the ODBC driver are you using (what are you reporting against: SQL
Server, Sybase, DB2, Oracle, etc?)
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rs90144" <rs90144@.hotmail.com> wrote in message
news:%23WCiwTN$EHA.3124@.TK2MSFTNGP11.phx.gbl...
> The message sound like some kind of math was done. But in the report there
> are
> no IIF funcatons or any other funcation, just a simple list of value from
> the ODBC driver. That's why it is so strange.
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:#VtBMFN$EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > Usually this is because on an expression being used that is doing math.
If
> > you use an iif function you need to be careful because every part of it
> > (both true and false) get evaluated which can cause overflow, divide by
0
> > type of errors.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "rs90144" <rs90144@.hotmail.com> wrote in message
> > news:%23n6J0kM$EHA.600@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > Has anyone encounter this error message when they try to preview the
> > report.
> > > I create a simple report with an external ODBC data source. The
report
> > has
> > > a list of name (string) and values (double).
> > > When I try to preview it keep on giving this error. I has no clue
what
> is
> > > causing this. I'ved try this ODBC on other application they all work
> > fines.
> > > It is just this new MSRS I am having trouble with.
> > > I am using VS 2003.Net with MSRS service patch 1.
> > >
> > > -rs
> > >
> > >
> >
> >
>|||I am using an in-house ODBC driver (Level-2.5 compliance). We tested this
ODBC driver with Crystal, Business Object, Excel etc. and even with
Microsoft Analysis Service and all works fine. It is just this new Reporting
Service that is giving us headache.
Any claues.
-rs
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eHQlidN$EHA.3368@.TK2MSFTNGP10.phx.gbl...
> What is the ODBC driver are you using (what are you reporting against: SQL
> Server, Sybase, DB2, Oracle, etc?)
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rs90144" <rs90144@.hotmail.com> wrote in message
> news:%23WCiwTN$EHA.3124@.TK2MSFTNGP11.phx.gbl...
> > The message sound like some kind of math was done. But in the report
there
> > are
> > no IIF funcatons or any other funcation, just a simple list of value
from
> > the ODBC driver. That's why it is so strange.
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:#VtBMFN$EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > > Usually this is because on an expression being used that is doing
math.
> If
> > > you use an iif function you need to be careful because every part of
it
> > > (both true and false) get evaluated which can cause overflow, divide
by
> 0
> > > type of errors.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "rs90144" <rs90144@.hotmail.com> wrote in message
> > > news:%23n6J0kM$EHA.600@.TK2MSFTNGP09.phx.gbl...
> > > > Hello,
> > > >
> > > > Has anyone encounter this error message when they try to preview the
> > > report.
> > > > I create a simple report with an external ODBC data source. The
> report
> > > has
> > > > a list of name (string) and values (double).
> > > > When I try to preview it keep on giving this error. I has no clue
> what
> > is
> > > > causing this. I'ved try this ODBC on other application they all work
> > > fines.
> > > > It is just this new MSRS I am having trouble with.
> > > > I am using VS 2003.Net with MSRS service patch 1.
> > > >
> > > > -rs
> > > >
> > > >
> > >
> > >
> >
> >
>