SSIS Data Connections

[dmando84]

Hello,

I created a package in SSIS that reads data from a text file and loads it into a table. The problem I am having is that I need to change the connection property in the data flow task to point to a database that is different to the database I created the package with. Whenever I change the database property in the connection object, I receive errors that I do not have permission etc. Has anyone had this problem before?

Thank you

 

[SQLBill]

What permissions do you have on the other database?


-SQLBill

Posting advice: FAQ481-4875

 

[dmando84]

Thanks for your quick response. I am an admin on both.

 

[manmaria]

When you tried to change the connection properties, did you try 'test connection'?

 

[Catadmin]

Can you post the errors?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dmando84]

Of course. I went through the proper steps of changing the database and the flat file it was pointing to. I am doing this in microsofts BIDS environment. Is there a way to refresh the database connection? It doesn't seem to be picking up the change, and we are looking right at it! VERY FRUSTRATION!

Thanks again...

 

[Catadmin]

Are you using variables or a configuration file?

Did you save the changes before trying to run them again?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dmando84]

I assume a configuration file, because I am not using variables. Here are the steps I took...

1. I created a new project in BIDS.
2. In the solution explorer I right clicked and added an existing package.
3. instanciated the package and clicked on the Data connection int the data flow task.
4.Changed the appropriate database fields.

 

[Catadmin]

Right click within the package, but away from any containers or tasks. See if Package Configurations in the popup list is greyed out. If not, click on it and see what type of configuration is listed in the resultant window.

Let us know that information along with posting the syntax of the errors you are getting.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[dmando84]

Thanks to everyone who responded. I created a test database on another server and I loaded 2 packages no problem. I was able to use the same connection and just change the database name. My packageds ran smooth as silk! I do not understand why. We decided to go with BCP anyway. Any ideas as to why this happened?


Thanks

Dave

 

[SQLBill]

Compare the permissions on both servers. Ping the remote server from both, maybe something is blocking the connection from one but not the other.

-SQLBill

Posting advice: FAQ481-4875