Data Download via ODBC

Server is SQL 2000 SP4. We are trying to pull some data from a Progress 9.1d
server via ODBC. If I go to the local packages, open the package in question
and execute it, everything runs fine and it gets the data. If I try and run
the job manually from the job list in the SQL server agent, it fails with
the following error showing in the job history:
Executed as user: <service account>. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Specified driver could
not be loaded due to system error 126 (MERANT 3.60 32-BIT Progress SQL92
v9.1D). Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file: Help context: 0 Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
string: Specified driver could not be loaded due to system error 126
(MERANT 3.60 32-BIT Progress SQL92 v9.1D). Error source: Microsoft OLE
DB Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution
complete. Process Exit Code 1. The step failed.
All I've been able to find about the error code 126 is the ODBC driver and
error -2147467259 seems to indicate a problem with the ODBC driver. But if
the driver was the problem, I don't understand why it only fails when run as
a SQL server agent job. I tried changing the service account to an admin
account instead, but it didn't make a difference.
Not sure where else to turn, so I'm hoping some people here might have some
ideas.
Thanks
Brian
First, when you execute a package manually it runs from your
PC and under your security context. When you schedule it as
a job, it runs on the server and under the security context
of the Agent service account if the job is owned by a
sysadmin (otherwise it executes under the proxy account
security context).
Did you install the Merant drivers on the server? Error 126
is "Module could not be found" and the other error you are
getting is for unable to load driver. Looks like all the
needed pieces may not be installed, setup on the server -
only on your PC.
-Sue
On Mon, 26 Feb 2007 16:23:15 -0500, "Brian"
<wouldntulike2know> wrote:

>Server is SQL 2000 SP4. We are trying to pull some data from a Progress 9.1d
>server via ODBC. If I go to the local packages, open the package in question
>and execute it, everything runs fine and it gets the data. If I try and run
>the job manually from the job list in the SQL server agent, it fails with
>the following error showing in the job history:
>Executed as user: <service account>. DTSRun: Loading... DTSRun:
>Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
>OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
>DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
>Error = -2147467259 (80004005) Error string: Specified driver could
>not be loaded due to system error 126 (MERANT 3.60 32-BIT Progress SQL92
>v9.1D). Error source: Microsoft OLE DB Provider for ODBC Drivers
>Help file: Help context: 0 Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 160 (A0) Error
>string: Specified driver could not be loaded due to system error 126
>(MERANT 3.60 32-BIT Progress SQL92 v9.1D). Error source: Microsoft OLE
>DB Provider for ODBC Drivers Help file: Help context: 0
>DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution
>complete. Process Exit Code 1. The step failed.
>All I've been able to find about the error code 126 is the ODBC driver and
>error -2147467259 seems to indicate a problem with the ODBC driver. But if
>the driver was the problem, I don't understand why it only fails when run as
>a SQL server agent job. I tried changing the service account to an admin
>account instead, but it didn't make a difference.
>Not sure where else to turn, so I'm hoping some people here might have some
>ideas.
>Thanks
>Brian
>
|||"Sue Hoegemeier" <Sue_H@.nomail.please> wrote
> First, when you execute a package manually it runs from your
> PC and under your security context.
Sorry I wasn't clear. Everything I am trying is on the server.

> Did you install the Merant drivers on the server?
Yes. They test and work correctly when I execute the package, but not when I
run the job logged on with the same authority. I also tried using the
service account authority and that didn't work either.
|||Try logging onto the server using the service account that
SQL agent runs under. While logged in using this account,
install the Merant drivers.
And it never hurts to run Component checker to make sure the
MDAC installation is correct. You can download the tool
from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx
-Sue
On Tue, 27 Feb 2007 09:47:50 -0500, "Brian"
<wouldntulike2know> wrote:

>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote
>Sorry I wasn't clear. Everything I am trying is on the server.
>
>Yes. They test and work correctly when I execute the package, but not when I
>run the job logged on with the same authority. I also tried using the
>service account authority and that didn't work either.
>

Data Conversion supported on Standard Edition?

I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:

DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"

We are running SQL Server 2005 Standard Edition 64-bit.

We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?

Also have a similar message for "Send Mail Task."

Is there anywhere that outlines what features are supported on each version?

What version of SQL Server are you running on your developer's workstation?|||

I'm running BIDS on my workstation which is connecting to our SQL Server. The same SQL Server where the integration package will not run from when imported into. So I'm actually not running a full blown SQL Server database on my workstation, just using BIDS on it along with Studio.

The Management Studio on my workstation is 9.00.3042.

SQL Server Integration Services is 9.00.3042. (Found by going to About > Help in Visual Studio)

Our SQL Server version is 9.00.3050

|||So both your workstation and the server are running SQL Server Standard edition? Not developer/enterprise edition?|||

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

I guess that would explain why it works on my workstation but not on the server?

|||

Erikk Ross wrote:

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

Run this query on the server and again on your local version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Yeah, like I said I don't have the actual Database engine installed on my workstation. But our server version is SP2: 9.00.3050.

So I guess Data Conversion is not supported in Standard Edition? Or send mail? Is there a matrix somewhere that shows what features in Integration Services are supported in each version of SQL Server? It would seem to me that data conversion is something that is pretty common...I'm a little suprised it would require the Enterprise edition.

|||Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.|||

Phil Brammer wrote:

Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.

That was indeed the solution. I just did a quick test and creating the package from a Standard edition version did allow the data conversion to work correctly. Thank you!!

|||

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

|||

Erikk Ross wrote:

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

Any NEW packages created still don't work?|||

Correct, I created a brand new package. Well I first tried to rebuild my old package but that didn't work either, so I just created a new one. Same problems, runs fine on my local workstation, but when imported into SQL Server gives the same error.

I am currently installing standard edition workstation components on a new workstation with Visual Studio and will try to create a package from there and import it and see if that works.

|||Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Phil Brammer wrote:

Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

9.00.3050.00 SP2 Standard Edition (64-bit)

Well now I am completely lost. I just installed the standard edition workstation components on a brand new machine. A machine that has never had SQL Server installed on it. I created a new package in Visual Studio, imported it into our SQL Server, ran it, and still get the same ProductLevelToLow errors.

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion".

|||

After reading this I realized my problem: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

I was running the package from my workstation using Management Studio, unaware that the package was actually running on my local workstation and not on the server. Since it was running on my workstation it required Integration Services to be installed which it wasn't, so that is why I got those error messages. As soon as I ran it directly from the server it worked fine.

*sigh* I wish this was more obvious. I thought that by running it in Management Studio it was just automatically running it on the server.

Data conversion inserting to DB2 on AS400 with SSIS

I created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.

When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.

For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as '?' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.

Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.

Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?

Thanks in advance for any help!

Try to set the appropriate LocaleID and DefaultCodePage on your destination component.

Thanks.

|||

The LocaleID and DefaultCodePage were set correctly.

What I found was in the Data Link Properties of connection manager, the "Host CCSID" was set to "OEM - United States [437]". I changed it to "EBCDIC - US/Canada [37]" and it started working perfectly.

Thank you for your suggestion though.

Data Conversion failed due to Potential Loss of data

Hi,

I am getting this error when my ssis package is running

Data Conversion failed due to Potential Loss of data

the input column is in string format and output is in sql server bigint

the error is occuring when there is an empty string in the input. what should i do to overcome this

It is an ID field and should i convert to bigint or should i leave it as char datatype is it i a good solution or is there a way to over come this.

Add a derived column to either change the empty string to NULL or a zero. Up to you, but you can't insert a string into an integer field.|||

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

|||

desibull wrote:

I am not sure why a string is being passed into a BigInt but I would not leave an input field null. I would use the Conditional operator ? : to provide the empty string a value of 0 if it is empty using the following in an expression:

ISNULL(<<input field>>) ? 0 : <<input field>>

In other words the above states that if the incoming field is NULL then fill it with a 0 otherwise pass the incoming value.

NULL and "empty string" are two very different things.

To expand on what I suggested earlier and desibull's code above:

ISNULL([InputColumn]) || [InputColumn] == "" ? 0 : [InputColumn]

OR

ISNULL([InputColumn]) || [InputColumn] == "" ? NULL(DT_I8) : [InputColumn]

Daily Import of file with date

Hello,

I have an SSIS package that imports daily files that have the date in the file name. I'd like my SSIS package to pickup today's file and run the package.

I've been using expression builder to create the file name but it drops the leading zeros in the month and the day.

My Import file looks like this: SRMSNotes_DATA_20080118.txt

Using this code: "D:\\importdata\\srmsdata\\SRMSNotes_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

SSIS looks for this file: SRMSNotes_DATA_2008118.txt

How do I make SSIS add the leading zeros?

Thanks for you help"D:\\importdata\\srmsdata\\SRMSNotes_DATA_" +
(DT_WSTR, 4) YEAR( GETDATE())

+
((DATEPART("mm",GETDATE()) ) < 10 ? ("0" + (DT_WSTR, 2)MONTH( GETDATE())) : (DT_WSTR, 2)MONTH( GETDATE()))

+
(DT_WSTR, 2) DAY( GETDATE() ) + ".txt"

You'd need to apply something similair for the DAY part|||To Get this:
20080213004235
YYYYMMDDHHMMSS

I use:
(DT_WSTR, 4) YEAR(GETDATE())
+
RIGHT(("0" + (DT_WSTR, 2) MONTH( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DAY( GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("hh", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("n", GETDATE())),2)
+
RIGHT(("0" + (DT_WSTR, 2) DATEPART("s", GETDATE())),2)

To Get this:
20080213004235
YYYYMMDDHHMMSS

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/

Daily build - trying to deploy from the command line

Hi

I'm trying to automate the build of a SSIS VisualStudio solution (ie I want to generate the deploy package automatically), but I get a weird error:

Error: Could not get a list of SSIS packages from the project.
========== Build: 0 succeeded, 1 failed, 0 up-to-date, 0 skipped ==========

I get this error when launching devenv with the following command-line:

devenv.exe MySolution.sln /Deploy development /Out build.log

Did anyone already meet this error ? How can I avoid that ?

regards

Thibaut
I should add that this error only appears when I set the CreateDeploymentUtility variable to true under the Deployment Utility section of the properties...|||Hi

Is there actually a anyone launching devenv.exe to generate the deployment packages ?

If no, is there any other known alternative ? (the idea is to automate the deployment package creation on a build server).

any hint will be most welcome.

regards

Thibaut|||

This link might help.

http://mgarner.wordpress.com/2006/08/31/automating-ssis-deployment/