Author |
Topic |
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 12:07:07
|
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
135 Posts |
Posted - 2012-12-10 : 12:28:53
|
try "Execute SQl Task" object |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 13:00:39
|
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. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-10 : 14:01:37
|
You need thishttp://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-10 : 14:28:46
|
If you are using linked servers do you really need 3 Connection Manager? |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 14:57:34
|
yes, i have three companies i need to query |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-10 : 15:02:29
|
You can also put this in Execute SQL TaskInsert into ServerName1.DatabaseName.SchemaName.TableNameSelect your QueryInsert into ServerName2.DatabaseName.SchemaName.TableNameSelect your QueryInsert into ServerName3.DatabaseName.SchemaName.TableNameSelect your Query Are you trying to sync up the data across 2 servers? |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 16:40:00
|
quote: Originally posted by sodeep You can also put this in Execute SQL TaskInsert into ServerName1.DatabaseName.SchemaName.TableNameSelect your QueryInsert into ServerName2.DatabaseName.SchemaName.TableNameSelect your QueryInsert into ServerName3.DatabaseName.SchemaName.TableNameSelect 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!! |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 16:41:02
|
Stupid question:What would I use for SchemaName.TableName? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-10 : 19:21:11
|
usually its dbo. If you want sync up then why don't you use replication for that table? |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 20:43:36
|
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. |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-10 : 20:50:32
|
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. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-10 : 21:31:51
|
Expand the table in MS and see what you have before tablename. That is schema for the table. |
|
|
theHydra
Starting Member
42 Posts |
Posted - 2012-12-11 : 09:57:22
|
It's dbo, but what about tableName? What would I use for that? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-11 : 10:14:04
|
Table where you are inserting into |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-12-11 : 11:43:37
|
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/ |
|
|
|