problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

problem with job

hy,

i have made a package that fills a sql server 2005 database. when i run it locally it runs all the way, no errors.

Now that i have deployed the package en work with a job that executes it every day i get these errors.

The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009.

and

component "Source - BU" (1) failed validation and returned error code 0xC020801C.

the strange thing is i don't get an error on the first dft which uses the same connection. i have pressed the test connection button and it states connection succesfull. any help would be appreciated.

Greetz,
Jens

It sounds like youre dynamically loading a connection that doesnt exist. Are you using a config file to load the connection info?

Have you tried logging on to the server machine (remote desktop), opening a command window and running the package through DTEXEC?

It will give you a more verbose error message which hopefully will flush out the issue.

(To run it with DTEXEC, open the job, select the SSIS step, click on the command line tab and copy and paste that text after DTEXEC)

|||

Are you by chance running the package on a 64bit server and is the source an Excel file or otherwise using the Jet engine for accessing the source?

Kirk Haselden
Author "SQL Server Integration Services"

|||

thanks for your reply's

i can run it with the dtexec utility without error

i'm using a access database to get the info. is this a problem?

|||

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround? Should i use a SQL 2000 server as an intermediate place for the import?

Many thanks

Jonathan

|||

FatherJack wrote:

Hi, I am using SQL 2005 Integration Services on a 64bit server and trying to import an Access 2000 database and get the above error when i debug the package. What is my best workaround?

Please check out this thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=430630&SiteID=1

Basically, you need to run the package in 32-bit mode.

Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.