SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SSIS Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  12:07:07  Show Profile  Reply with Quote
Hi all,

I'm working on my first SSIS project. Ihave all my DB connections defines and configured, I'm now at the point where I;m trying to choose my control flow items.

Basically what I'm trying to do is run the same SQL code against three identical DB's stored on three different SQL server. So that's where I'm at. Which of the tasks in the control flow items would be best for what I'm trying to do?

Thanks all.

subhaoviya
Posting Yak Master

India
135 Posts

Posted - 12/10/2012 :  12:28:53  Show Profile  Reply with Quote
try "Execute SQl Task" object
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  13:00:39  Show Profile  Reply with Quote
Actually I spoke too soon. My connections are not set right, I have all three databases set up in SSIS, but realized I need to use linked servers, which I now have set up in SQL SMS.

With that said, how do I now configure my SSIS project to look at the three DBs so when I execute the query, it runs against all three DB's, not just one?

Thanks again.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  14:01:37  Show Profile  Reply with Quote
You need this
http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/10/2012 :  14:28:46  Show Profile  Reply with Quote
If you are using linked servers do you really need 3 Connection Manager?
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  14:57:34  Show Profile  Reply with Quote
yes, i have three companies i need to query
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  15:02:29  Show Profile  Reply with Quote
You can also put this in Execute SQL Task

Insert into ServerName1.DatabaseName.SchemaName.TableName

Select your Query

Insert into ServerName2.DatabaseName.SchemaName.TableName

Select your Query

Insert into ServerName3.DatabaseName.SchemaName.TableName

Select your Query


Are you trying to sync up the data across 2 servers?
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  16:40:00  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

You can also put this in Execute SQL Task

Insert into ServerName1.DatabaseName.SchemaName.TableName

Select your Query

Insert into ServerName2.DatabaseName.SchemaName.TableName

Select your Query

Insert into ServerName3.DatabaseName.SchemaName.TableName

Select your Query


Are you trying to sync up the data across 2 servers?



This looks promising!!!

I'm not trying to sync, I just want to query all DB's when this report is ran. It's for a high level exec, so he doesn't wan to run the report for each of the companies then add up the totals, he wants the report to do it automatically.

Thanks!!
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  16:41:02  Show Profile  Reply with Quote
Stupid question:

What would I use for SchemaName.TableName?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  19:21:11  Show Profile  Reply with Quote
usually its dbo. If you want sync up then why don't you use replication for that table?
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  20:43:36  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

usually its dbo. If you want sync up then why don't you use replication for that table?



dbo.tableName? table name of the table i want to create or table name of a table in the DB i'm querying? That part is confusing me.

I'm not sure whether i need to replicate or sync, for now I'm just trying to figure out how to get this set up first, i may be in over my head, I'm learning SSIS as I go...as you can tell.
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/10/2012 :  20:50:32  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

usually its dbo. If you want sync up then why don't you use replication for that table?



dbo.tableName? table name of the table i want to create or table name of a table in the DB i'm querying? That part is confusing me.

I'm not sure whether i need to replicate or sync, for now I'm just trying to figure out how to get this set up first, i may be in over my head, I'm learning SSIS as I go...as you can tell.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/10/2012 :  21:31:51  Show Profile  Reply with Quote
Expand the table in MS and see what you have before tablename. That is schema for the table.
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 12/11/2012 :  09:57:22  Show Profile  Reply with Quote
It's dbo, but what about tableName? What would I use for that?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/11/2012 :  10:14:04  Show Profile  Reply with Quote
Table where you are inserting into
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/11/2012 :  11:43:37  Show Profile  Reply with Quote
Just as an FYI, the "ServerName.DatabaseName.SchemaName.TableName" is called 4-part naming. Here is a link that describes it in more detail:
http://www.mssqltips.com/sqlservertip/1095/sql-server-four-part-naming/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000