Avatar of stuntwoman
stuntwoman asked on

SSIS Package will not run as a SQL Server Job

I have a SSIS package which runs OK through VisualStudio it imports some data from a FoxPro database, however when I deploy it as a job on SQL Server 2005 I get the error

" Description: The AcquireConnection method call to the connection manager "FoxProConn" failed with error code 0xC0202009.  End Error "

I have other SSIS packages that run OK as jobs, importing from excel etc so I don't think it is  a permissions thing - I am running it as a cmd.exe with this

"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "\\pq\SSISTester.dtsx"    /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

is it something to do with the Fox Pro OLE DB??

Any pointers would be appreciated

Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005FoxPro

Avatar of undefined
Last Comment
CarlWarner

8/22/2022 - Mon
brad2575

Is the computer you are running the SSIS in visual studio the same PC as the SQL server is on?

If not the FoxPro Conn may not be set up on the Server where SQL is running.
ASKER CERTIFIED SOLUTION
CarlWarner

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
stuntwoman

When I run the SSIS in visual studio it is on my local machine, when running as a job it is on a server.

Surely all the connection string information is held in the dtsx package? My other packages work OK with no further setup required - the ones importing from excel or csv files.

Thanks
ASKER
stuntwoman

I have the latest OLE DB Provider for VFP on my local machine - does it have to be on the sql server or does the dtsx package take care of that?

Thanks for your help
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
stuntwoman

thanks - I will try these things out and let you know
ASKER
stuntwoman

This is fixed now it was a combination of  two things, I got the latest dll and sorted the permissions onto the server

Thanks for the help
CarlWarner

You're welcome.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.