Connection String -- Bulk Load

[sdempsey]

Hi,

I had a DTS package on a local server and successfully completed a bulk load transfer. By connecting to the file using the following code:-

objXBulkLoad.ConnectionString = "PROVIDER=SQLOLEDB.1;SERVER=(local);Trusted_Connection=Yes;DATABASE=Project_Database;"

I now need to move this over to another server which is not on my local machine. I am not sure what I need to do to allow the package to work.I have created an account with a username and password. Tried changing it to:-

objXBulkLoad.ConnectionString = "provider=SQLOLEDB.1;data source=SERVER NAME ;database=Project_Database;uid=User;pwd=Password"

This is the part of the code which it is failing on and stating that it cannot connect to the data source.

Thanks Sarah

 

[NoCoolHandle]

OK questions.


Does the server "Server Name" allow sql connections?
Are you working in a domain envorinment?

Have you tried User Id=User and Password=password.
(the uid and pwd should work, but...)

If you try to connect to the other system "SEVER NAME" using a sqlconnection and that username/password?

Can the PC you are executing the package from see "Server Name"? Ie. what happens when you ping it..

 

[sdempsey]

There was no problem with the connection string the user did not have enough permissions.

Thanks

 

[NoCoolHandle]

Ok, then it must be a straight permission issue.

From reviewing the rolememebership, I suspect you will need to be a member of the db_owner Role.

How does your Login ID map to USerID's in the database.

Are you dbo level or has that changed since you shifted the db to the other server?
Rob

 

[gmmastros]

You may find your answer here.

http://www.connectionstrings.com/

Or you might not. At least give it a shot.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom