Connection Manager in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Connection Manager in SSIS

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-10 : 18:43:00

I have set up a connection manager to one database in my Data Flow. Also, in my data flow I have a SQL Execute task where I need to connect to a different database and table that's on another server. How do I set that connection up?

daniness
Starting Member

3 Posts

Posted - 2011-10-10 : 21:55:14

Have you tried adding another connection manager and pointing it to the appropriate server and table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 01:42:05

if databases are on same server you can even refer to that table inside execute sql task like below

SELECT column FROM [Other DB Name].dbo.table

this will work even if connection manager points to different db.
But you should do this only if you want to join to this table for getting some business logic. If you want some independent data better to put it in separate execute sql task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-11 : 11:04:49

Thanks for both of your replies. The databases are on different servers. Here's part of the query in the SQL Task:


(Select t.doc, ISNULL(COUNT(distinct t.clm), 0)as pendinfo1
from t2dibpend t
where DIB_MVT_SEQ_NUM IS NULL and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null)
group by t.doc)a
on n.doc = a.doc

I need to get to the people table on the other server.

I added this connection string to that table but when I run it in BIDS it doesn't open it. What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:45:36

what do you mean by it doesnt open? is it giving some error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-11 : 13:50:43

What I've done is deploy the job then I imported it into MSDB. Then I created an Agent Job and when I schedule it it just continues to run and never succeeds. It's because I don't have access to that database on the different server. This runs fine if I run it on my local drive in BIDS.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 14:20:35

even if you dont have access to other server if you've configured a service account for job which has access to other server, then it should work fine from the job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-11 : 14:35:14

I don't know why it won't open.

Here's my connection string in SSIS. The first one works well. I need access to the second one to run that stored procedure I am trying to gain access to the DB.

Data Source=seb2a54;Initial Catalog=PendingClaims;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Data Source=seb2a21;Initial Catalog=SpecNew;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

when I went into Mgt Studio in 2005 and tried to add my user to the database on seb2a21 I don't see it listed there. Am I doing something wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:25:02

how were you trying to add user? using wizard or using T-sql statement?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-12 : 10:46:07

This is a permissions problem. I asked the DBA to add my user and it worked. I also added a connection manager for both servers, broke the stored procedure up and ran it and it still isn't picking up. My DBA will have to take a look as to why I can't get to a specific server. I even added my username and password which doesn't work either. Thanks for you help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:06:04

whats the errors its through? still access issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-13 : 09:33:58

No errors the job just continues to run and never stops. I found out my user was not on the other server so I didn't have access to the other database.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-17 : 16:24:59

Okay I found the problem

I needed the bold part so I can get onto the other database and table. Why is that because the bold part is a join and I needed that to run the SQL Agent Job?

(Select t.doc, ISNULL(COUNT(distinct t.clm), 0)as pendinfo1
from t2dibpend t
where DIB_MVT_SEQ_NUM IS NULL and t.cos not in (select cos from seb2a21.specnew.dbo.people where completedt is null and t.cos=cos)
group by t.doc)a
on n.doc = a.doc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:24:57

oh ok without condition there's no relation between subquery and outside part so i think it ended up seraching against all records in seb2a21.specnew.dbo.people where completedt is null

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 09:38:13

Yes so that's why my Agent continued to run. Thanks so much for taking the time to assist me and all of your explanations you give me as well. You've taught me so much! All of you guys have!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources