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
 General SQL Server Forums
 New to SQL Server Programming
 SSIS Question

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
Go to Top of Page

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.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

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

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?
Go to Top of Page

theHydra
Starting Member

42 Posts

Posted - 2012-12-10 : 14:57:34
yes, i have three companies i need to query
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-10 : 15:02:29
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 - 2012-12-10 : 16:40:00
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 - 2012-12-10 : 16:41:02
Stupid question:

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

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-11 : 10:14:04
Table where you are inserting into
Go to Top of Page

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/
Go to Top of Page
   

- Advertisement -