Queries using tables from diffrent Databases or SQL instances

Hello,

I am new in SSIS.

I am using an OLEDB source and setted as SQL Command.

The Query is a JOIN between different databases.

How can I make the QUERY with different source (different databases or SQL Servers)?

I mean, any solution is OK, the important is to make queries against different databases with SSIS.

Thank

You can always use two or more OLE DB sources and then use a union all or Merge Join transformations.|||

As Phil wrote in the previous post, you can add several OLEDB sources or others sources and join the data adding a UNION ALL or MERGE JOIN.

In merge Join the input data must be sorted and could ne joined by LEFT or RIGHT.

Regards!

|||

Thank|||

If you have to use an Execute SQL Task, you can do this by creating Linked servers, it can be SQL server or not.

Then use fully qualified names.

I use this to compare tables content side by side accross similar servers.

i.e.

Select s1.ColumnA as Server1_Status, s2.ColumnA as Server2_Status

From Server1 s1

Join Server2 s2 on s1.Pkey = s2.Pkey

Where Server2 is an entry in Server Objects, Linked Servers

For everything else, I use the other options like multiple pumps and Union All.

It performs better, especially when pulling from non-SQL databases.

Regards,

Philippe

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?
You can add views to your database which include the database name for the
external objects then you can just access the view name.
It means releasing different views in the different envronments but the rest
of the code will stay the same.
"stuckish@.gmail.com" wrote:

> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>
|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
[vbcol=seagreen]
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegrou ps.com...
|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegro ups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?You can add views to your database which include the database name for the
external objects then you can just access the view name.
It means releasing different views in the different envronments but the rest
of the code will stay the same.
"stuckish@.gmail.com" wrote:

> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
[vbcol=seagreen]
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>
>

Queries between databases

We have some diffrent databases on our servers and we sometimes want to
make queries between them. The problem is that we have diffrent names
for the databases. In the dev enviroment the databases is called
database1_dev and in stage it's called database2_stage and so on.
What is the best way to create some sort of alias or something so that
i can add a query to dev that i don't have to change when i move it to
stage.
For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
As it is now i must have SELECT * FROM database1_dev.table1 JOIN
database2_dev.table1 ..
And i have to change it on deployment. There must be some good solution
for this right?What is the version are you using?
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||if you deploy your database using SQL Scripts, you can use the sqlcmd
commandline tool to execute these scripts.
using this tool you can use variables like:
$(db1) and $(db2)
finally the query used to access the 2 databases will be:
SELECT * FROM $(db1).table1 JOIN $(db2).table1 ON ..
when you execute the script and you change the db1 & 2 variables values,
your query will use the correct names.
but this works fine only using the sqlcmd tool and only after you modify the
SQL script to replace database1 by $(db1).
<stuckish@.gmail.com> wrote in message
news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> We have some diffrent databases on our servers and we sometimes want to
> make queries between them. The problem is that we have diffrent names
> for the databases. In the dev enviroment the databases is called
> database1_dev and in stage it's called database2_stage and so on.
> What is the best way to create some sort of alias or something so that
> i can add a query to dev that i don't have to change when i move it to
> stage.
> For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> database2_dev.table1 ..
> And i have to change it on deployment. There must be some good solution
> for this right?
>|||Thanks for all the replies .. im sure i will go with one of the
methods.
We are using SQL Server 2005 (Enterprise i think) ..
Br, Ola
Uri Dimant wrote:
> What is the version are you using?
>
> <stuckish@.gmail.com> wrote in message
> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
> > We have some diffrent databases on our servers and we sometimes want to
> > make queries between them. The problem is that we have diffrent names
> > for the databases. In the dev enviroment the databases is called
> > database1_dev and in stage it's called database2_stage and so on.
> >
> > What is the best way to create some sort of alias or something so that
> > i can add a query to dev that i don't have to change when i move it to
> > stage.
> >
> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
> >
> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
> > database2_dev.table1 ..
> >
> > And i have to change it on deployment. There must be some good solution
> > for this right?
> >|||Take look at SYNONYM command as well
<stuckish@.gmail.com> wrote in message
news:1167829345.028355.29210@.i12g2000cwa.googlegroups.com...
> Thanks for all the replies .. im sure i will go with one of the
> methods.
> We are using SQL Server 2005 (Enterprise i think) ..
> Br, Ola
> Uri Dimant wrote:
>> What is the version are you using?
>>
>> <stuckish@.gmail.com> wrote in message
>> news:1167825230.835591.72840@.42g2000cwt.googlegroups.com...
>> > We have some diffrent databases on our servers and we sometimes want to
>> > make queries between them. The problem is that we have diffrent names
>> > for the databases. In the dev enviroment the databases is called
>> > database1_dev and in stage it's called database2_stage and so on.
>> >
>> > What is the best way to create some sort of alias or something so that
>> > i can add a query to dev that i don't have to change when i move it to
>> > stage.
>> >
>> > For example SELECT * FROM database1.table1 JOIN database2.table1 ON ..
>> >
>> > As it is now i must have SELECT * FROM database1_dev.table1 JOIN
>> > database2_dev.table1 ..
>> >
>> > And i have to change it on deployment. There must be some good solution
>> > for this right?
>> >
>