Multiple Instance of DTS

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

javawaba wrote:

I have a DTS package I created that is dadabase driven to process source data from multiple sources.

Can I run several instances of this package in parallel for each source or would I need to create a copy for each source ?

JavaWaba

This isn't a DTS forum. I recommend you direct your question to the DTS newsgroup microsoft.public.sqlserver.dts

Multiple DataSources on a DataSourceView

I have added multiple tables from different data sources to a dsv. When I try to make a model from that DSV, it throws an error, saying that it can't find any of my tables that are not the primary tables. Is this a bug or am I missing something that I should be doing?

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

OK

Thanks!

Nathan

I have done a bit of research on the issue and I am now thinking it may be a problem w/ my databases, but I am not sure. What I do need is to confirm that someone has used DSV's that contain multiple datasources with success. Thanks!

Nathan

Multiple DataSources on a DataSourceView

I have added multiple tables from different data sources to a dsv. When I try to make a model from that DSV, it throws an error, saying that it can't find any of my tables that are not the primary tables. Is this a bug or am I missing something that I should be doing?

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

OK

Thanks!

Nathan

I have done a bit of research on the issue and I am now thinking it may be a problem w/ my databases, but I am not sure. What I do need is to confirm that someone has used DSV's that contain multiple datasources with success. Thanks!

Nathan

Multiple Databases one report

I have two DBs that are identical (MOM2K5 SCDW) and I want to have both as
data sources for reports. I have setup a second data source and I don't see
the info I excpect. If I take the same connection string and enter it into
the original data source it gets info from my second DB so I know the
connection string on the second data source is ok. Do I have to do something
other than simply create the second data source? I was really excited
because after working on this for close to forever it was all working.
Unfortunately, I came in the next morning and it wasn't. I'm at an impass
and would appreciate any help I can get.There is some information on modifying your datasource (or connection string)
on the fly in this section:
Search for: datasource "on-the-fly"
THis will allow you to specify your datasource as a parameter.
"Scottshark" wrote:
> I have two DBs that are identical (MOM2K5 SCDW) and I want to have both as
> data sources for reports. I have setup a second data source and I don't see
> the info I excpect. If I take the same connection string and enter it into
> the original data source it gets info from my second DB so I know the
> connection string on the second data source is ok. Do I have to do something
> other than simply create the second data source? I was really excited
> because after working on this for close to forever it was all working.
> Unfortunately, I came in the next morning and it wasn't. I'm at an impass
> and would appreciate any help I can get.

Multiple data sources, single cube?

Newbie question...

I need to create a report with data coming from two entirely separate data sources, the only common factor being the date.

I can see that I could create a single DSV and cube by using views to point to the second database. But is it considered best practice to create a second cube and join the cubes with MDX?

Thanks

This is not so much of a technical question as a how you solve these problems generally. If there is a point of joining information in separate sources you should do it, not in the cubes, but in a data warehouse.

These integration problems should be handled in extract-, transfer-, load processes before you build a cube.

Have a look here(http://www.kimballgroup.com/) for support and ideas.

HTH

Thomas Ivarsson

|||

Thanks Thomas,

That ties in with advice from the Project REAL docs too.

Multiple Data Sources in a Single Data Set

Within "Report Designer", is there a way to query multiple Data Sources within a single Data Set query? I need to build a query in a Data Set that joins three tables from different Servers & Databases. From what I can tell, each Data Set query can only access a single Data Source.better you create a stored procedure in the backend . where stored procedure can interact with differenct databases and you will get your dataset.

Multiple data sources in a report model

I've built several data view sources and report models and think its great.
But this week I'v ran into problems. I'm trying to implementent a report
model that uses data from three databases. I've managed to build the data
source view containing tables from all three different data sources, but when
I try to build the report model it fails. It seems that it can't find tables
in other data sources that the primary.In SQL Server 2005, a report model can have only one data source file, one
data source view file, and one smdl file. What you are attempting to do is
not a supported scenario in this release.
"Erik Hedlund" wrote:
> I've built several data view sources and report models and think its great.
> But this week I'v ran into problems. I'm trying to implementent a report
> model that uses data from three databases. I've managed to build the data
> source view containing tables from all three different data sources, but when
> I try to build the report model it fails. It seems that it can't find tables
> in other data sources that the primary.|||Hmm, this is very limiting, because the data source view can contain tables
from several datasources. It's rather odd that the report model should be
concerned about lies bellow the data source view abstraction layer.
"Katmac [MS]" wrote:
> In SQL Server 2005, a report model can have only one data source file, one
> data source view file, and one smdl file. What you are attempting to do is
> not a supported scenario in this release.
> "Erik Hedlund" wrote:
> > I've built several data view sources and report models and think its great.
> > But this week I'v ran into problems. I'm trying to implementent a report
> > model that uses data from three databases. I've managed to build the data
> > source view containing tables from all three different data sources, but when
> > I try to build the report model it fails. It seems that it can't find tables
> > in other data sources that the primary.|||Multiple data source support is on the wish list for a future release.
"Erik Hedlund" wrote:
> Hmm, this is very limiting, because the data source view can contain tables
> from several datasources. It's rather odd that the report model should be
> concerned about lies bellow the data source view abstraction layer.
> "Katmac [MS]" wrote:
> > In SQL Server 2005, a report model can have only one data source file, one
> > data source view file, and one smdl file. What you are attempting to do is
> > not a supported scenario in this release.
> >
> > "Erik Hedlund" wrote:
> >
> > > I've built several data view sources and report models and think its great.
> > > But this week I'v ran into problems. I'm trying to implementent a report
> > > model that uses data from three databases. I've managed to build the data
> > > source view containing tables from all three different data sources, but when
> > > I try to build the report model it fails. It seems that it can't find tables
> > > in other data sources that the primary.|||If the product doesn't allow a DSV to have multiple data sources, then you
shouldn't be able to save a DSV that references more than 1 DS. I just
wasted three hours trying to track down this "bug." Certainly would have
saved me time had VS just told me it didn't support more than 1 data source
in a dsv. instead, I got a cryptic message at model generation time that it
coudln't find a table.
"Katmac [MS]" wrote:
> Multiple data source support is on the wish list for a future release.
> "Erik Hedlund" wrote:
> > Hmm, this is very limiting, because the data source view can contain tables
> > from several datasources. It's rather odd that the report model should be
> > concerned about lies bellow the data source view abstraction layer.
> >
> > "Katmac [MS]" wrote:
> >
> > > In SQL Server 2005, a report model can have only one data source file, one
> > > data source view file, and one smdl file. What you are attempting to do is
> > > not a supported scenario in this release.
> > >
> > > "Erik Hedlund" wrote:
> > >
> > > > I've built several data view sources and report models and think its great.
> > > > But this week I'v ran into problems. I'm trying to implementent a report
> > > > model that uses data from three databases. I've managed to build the data
> > > > source view containing tables from all three different data sources, but when
> > > > I try to build the report model it fails. It seems that it can't find tables
> > > > in other data sources that the primary.

Multiple Data sources for a dataset

Hey everyone,

I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?

thanks,

Keith

Not within the report I'm afraid.

However when building your own application, if you use the report viewer control then you can use .Net datasets to populate data sources. You could therefore populate the dataset manually in C-sharp or VB from the various data sources and pass that to the report viewer control.

The other option is to write your data delivery extension using the reporting services extensible object model.

|||What are the datasources? If they are two different database systems (SQL Server, Oracle, etc), you can consider using linked servers to combine the data before SSRS ever receives it. Just be aware of the performance implications of doing so.

HTH...

Joe

Multiple Data Sources for 1 Dataset?

I have to create a join between tables on SQL Server & a table/file on an
AS400(DB2).
I can create the 2 different data sources just fine, but I cannot figure out
how to join the data into 1 dataset, since you can only use 1 data source.
Has anyone been able to do this? Help!Hod did you go with this question Lori?
I have the same problem.
Thanks
Todd
"Lori" wrote:
> I have to create a join between tables on SQL Server & a table/file on an
> AS400(DB2).
> I can create the 2 different data sources just fine, but I cannot figure out
> how to join the data into 1 dataset, since you can only use 1 data source.
> Has anyone been able to do this? Help!|||I ended up having to create a linked server (on SQL Server) to connect to the
AS/400. I then used a SP to do the query statement. Let me know if you can
find any better way. The linked server does work, it's just not pretty. :-)
"Tango" wrote:
> Hod did you go with this question Lori?
> I have the same problem.
> Thanks
> Todd
> "Lori" wrote:
> > I have to create a join between tables on SQL Server & a table/file on an
> > AS400(DB2).
> > I can create the 2 different data sources just fine, but I cannot figure out
> > how to join the data into 1 dataset, since you can only use 1 data source.
> > Has anyone been able to do this? Help!|||A painfully learned suggestion. Even though with 4 part naming you can do a
heterogenous join, don't. It will pull on the data from the tables locally
to do the join. Use openquery, bring the appropriate data into a temp table
and then join with the temp table. If you have a query with a join (regular
join, not heterogenous) or you have a parameter then even with all the
tables in the query going against the same database it is likely to bring
over all the data locally to process the result. Hence my strong suggestion
to stay away from 4 part naming and use openquery (even though a pain)
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lori" <Lori@.discussions.microsoft.com> wrote in message
news:39D2A369-C0EC-44E1-B5AB-48A78DA74DB9@.microsoft.com...
>I ended up having to create a linked server (on SQL Server) to connect to
>the
> AS/400. I then used a SP to do the query statement. Let me know if you
> can
> find any better way. The linked server does work, it's just not pretty.
> :-)
> "Tango" wrote:
>> Hod did you go with this question Lori?
>> I have the same problem.
>> Thanks
>> Todd
>> "Lori" wrote:
>> > I have to create a join between tables on SQL Server & a table/file on
>> > an
>> > AS400(DB2).
>> > I can create the 2 different data sources just fine, but I cannot
>> > figure out
>> > how to join the data into 1 dataset, since you can only use 1 data
>> > source.
>> > Has anyone been able to do this? Help!

Multiple data sources applied to the same report row

Woulf anyone know, if RS would work with multiple data sources applied to
the same report. For example, in a report, there are two data sources
pointing to different servers and few of the report columns are associated
with one data source and few with the other. How RS can handle this sort of
requirement because supposedly there is no option in RS to create a dataset
that is based on multiple data sources.
There are few alternatives like using linked server logic or sub reporting
however we are unable to find any direct way of accomplishing it.You can create as many data sources as you want in each report.
You can only use 1 query per table or graph though. I put some of my tables
next to each other, to look like 1 table in some of my reports.
"Mike Hernandez" <mike@.newage.com> wrote in message
news:%23ZFy0OEwFHA.256@.TK2MSFTNGP15.phx.gbl...
> Woulf anyone know, if RS would work with multiple data sources applied to
> the same report. For example, in a report, there are two data sources
> pointing to different servers and few of the report columns are associated
> with one data source and few with the other. How RS can handle this sort
of
> requirement because supposedly there is no option in RS to create a
dataset
> that is based on multiple data sources.
> There are few alternatives like using linked server logic or sub
reporting
> however we are unable to find any direct way of accomplishing it.
>|||Hi Mike,
Could you tell me how to impose relations between two Datasets.
Regards
AmitK
Cindy Lee wrote:
> You can create as many data sources as you want in each report.
> You can only use 1 query per table or graph though. I put some of my tables
> next to each other, to look like 1 table in some of my reports.
>
> "Mike Hernandez" <mike@.newage.com> wrote in message
> news:%23ZFy0OEwFHA.256@.TK2MSFTNGP15.phx.gbl...
> > Woulf anyone know, if RS would work with multiple data sources applied to
> > the same report. For example, in a report, there are two data sources
> > pointing to different servers and few of the report columns are associated
> > with one data source and few with the other. How RS can handle this sort
> of
> > requirement because supposedly there is no option in RS to create a
> dataset
> > that is based on multiple data sources.
> >
> > There are few alternatives like using linked server logic or sub
> reporting
> > however we are unable to find any direct way of accomplishing it.
> >
> >

Multiple data sources

If I write an SQL query I can select data from various database from within
the same query. Any idea how I can do that when creating a SRS report.?
ThanksThe best way is by creating a stored procedure on the SQL Server, which
picks up the data from the different databases. Then you use this stored
procedure in your report.
Kaisa M. Lindahl Lervik
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
> If I write an SQL query I can select data from various database from
> within the same query. Any idea how I can do that when creating a SRS
> report.?
> Thanks
>|||Great.
thanks very much.
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:es0rtfoAHHA.3604@.TK2MSFTNGP04.phx.gbl...
> The best way is by creating a stored procedure on the SQL Server, which
> picks up the data from the different databases. Then you use this stored
> procedure in your report.
>
> Kaisa M. Lindahl Lervik
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
> news:O8Rh%23nmAHHA.3560@.TK2MSFTNGP04.phx.gbl...
>> If I write an SQL query I can select data from various database from
>> within the same query. Any idea how I can do that when creating a SRS
>> report.?
>> Thanks
>

Multiple Data Sources

Can I create a report from multiple data sources?
For example can I create a report that pulls Client ID's from SQL and
include data from an Access DB source that had the same Client ID's?Yes create the different datasources using data tab and put the data in the
layout using two datasets.
Amarnath
"Joe" wrote:
> Can I create a report from multiple data sources?
> For example can I create a report that pulls Client ID's from SQL and
> include data from an Access DB source that had the same Client ID's?
>
>

Multiple Data Sources

Is there away to change the datasource at the time of deployment of a report? I know there is a production and debug deployment for specifying URLs and Folders. Is there anyway to specify the datasource, so you can have one for production and another for debug / test? Do you keep two datasources in the project? The production datasource already exists in the target environment.

Any help would be appreciated.

Thanks!

DotNetNow

Well:

We keep (as I think all people do) environments for test and production - so we test everything with RS and then deploy to the production server and just change (or point to the production environment

within the Project Properties or within the data sources within the Project itself such as:

Data Source=(For Example the Server Name);Initial Catalog=(For Example the data base name)

That means that when a developer is done doing what a developer does - the DataSource Name is changed to the QA group - all applications are deployed to the QA group -

The QA group does what they do and they have a production person change the Data Source parameters to the production environment.

That is one of the features I really like with RS is the deployment capabilities - it all relies on what you define within the data sources within the project or the properties of the project.

Hope this helps!