Can not created Named Calculation...

I can not seem to create a Named Calculation on a table that I have already created a Named Query on in my data source view. Any idea why this is and if there is a work around for it?

TIA

Wellman

If you have already a Named Query you can add an expression-based column to the SELECT statement using the syntax of the underlying data source.|||

Thank you for this information. Any idea why this is so? Doesn't make much sense to me.

Also, can I reference other tables as well?

TIA

Wellman

|||Which part doesn't make sense? You can view a named query as a SQL view. Just like a SQL view, your SELECT statement can include any valid syntax, including other tables, calling UDF, etc.

Can not create connection to data source

Hi there,
I've got Reporting services is installed on server 1 and sql server &
analysis services installed on server2. I've published my reports on server1.
When i try to view my reports from report manager i get error msg "Can not
create connection to data source". I've got four reports. Three are based on
cubes and one on sql databases. The one with sql database works fine but any
reports with olap db just doesn't work.
Can anyone help please? I'm trying to fix this for a while but can't get it
working.
Your help is appreciated..
--
Regards,
VivekHi there,
Just to add some more info..i'm using sql2000.
--
Regards,
Vivek
"Vivek" wrote:
> Hi there,
> I've got Reporting services is installed on server 1 and sql server &
> analysis services installed on server2. I've published my reports on server1.
> When i try to view my reports from report manager i get error msg "Can not
> create connection to data source". I've got four reports. Three are based on
> cubes and one on sql databases. The one with sql database works fine but any
> reports with olap db just doesn't work.
> Can anyone help please? I'm trying to fix this for a while but can't get it
> working.
> Your help is appreciated..
> --
> Regards,
> Vivek|||I have a simular setup as you and am running into the same issues. ServerA
SSRS 2005, ServerB SQL Server 2000 with Analysis Services. Reports built
from SQL Server databases are able to be displayed, reports built from AS
give error 'Cannot create a connection to data source <InventoryCube>'. I've
tried changing security settings on the data souce with in report manager
with out any luck. Data source is currently set to 'Windows Integrated
Security'
"Vivek" wrote:
> Hi there,
> Just to add some more info..i'm using sql2000.
> --
> Regards,
> Vivek
>
> "Vivek" wrote:
> > Hi there,
> >
> > I've got Reporting services is installed on server 1 and sql server &
> > analysis services installed on server2. I've published my reports on server1.
> > When i try to view my reports from report manager i get error msg "Can not
> > create connection to data source". I've got four reports. Three are based on
> > cubes and one on sql databases. The one with sql database works fine but any
> > reports with olap db just doesn't work.
> >
> > Can anyone help please? I'm trying to fix this for a while but can't get it
> > working.
> >
> > Your help is appreciated..
> >
> > --
> > Regards,
> >
> > Vivek

Can not create a connection to Oracle data source when report is processing

hello,

I am getting following error message when I try to view a report from reporting services. The report runs fine but after while i get the following error message. The error message will go away if i open the report manage from the server and run the report once. The report will run fine for a while from anywhere but afterwhile i get following error message.

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source 'OracleConnProc'. (rsErrorOpeningConnection)

Error while trying to retrieve text for error ORA-12154

can someone please help me.

Most likely there is a problem with the file system permissions of your Oracle client installation on the report server machine. The file system permissions probably prevent the account configured to run the RS Web Service to load certain Oracle client configuration files.

Have you looked at the following KB article? http://support.microsoft.com/default.aspx?scid=kb;en-us;870668

-- Robert

|||

Hello Robert,

I followed steps from your suggested article. I am still having same problem, but at the same time I could not verify my changes as suggested in the article.

Note To verify the configured account for the Reporting Services Web Service, you can open the RSReportServer.config file. You will find informaiton that is similar to the following:

<WebServiceAccount>NT Authority\NetworkService</WebServiceAccount>

I was thinking to reinstall oracle client on my reporting server, but please let me know if you have any other suggestion.

Thanks,

Vimal

Can more than one database be queried

Can more than one database be queried when creating a report in SQL Server 2005 if they are from the same data source?

Our company is going to SQL Server 2005 for a bunch of Access databases and a C++ system. Would like to be able to query these different database at the same time to create the report. Is it possible to do this or do we need another reporting service?

Thank you for any help,

Jennifer

Yes, you can have multiple datasets based on one data source, or multiple datasets based on multiple data sources. See this page for more information on this http://msdn2.microsoft.com/en-us/library/ms160324.aspx

Can I use this thing to import data from a table into a view.

Hi all,
I need to import data from a source table(table is in Sql) into a view. I
need to insert, delete or update the view to make sure all rows in the view
have the same data as the source table. Can you please give me how to design
this in a Integration Services project?
Thanks.Views contain no data -- there is nothing to insert, delete, or update.
Simply create the view, and every time you access it, the base tables
involved in the view will be queried. Therefore, the view will always have
the same data as the base tables.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"someone" <nospam@.stopspam.com> wrote in message
news:OusidbIcGHA.3840@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to import data from a source table(table is in Sql) into a view.
> I need to insert, delete or update the view to make sure all rows in the
> view have the same data as the source table. Can you please give me how to
> design this in a Integration Services project?
> Thanks.
>

Can I use this thing to import data from a table into a view.

Hi all,
I need to import data from a source table(table is in Sql) into a view. I
need to insert, delete or update the view to make sure all rows in the view
have the same data as the source table. Can you please give me how to design
this in a Integration Services project?
Thanks.Views contain no data -- there is nothing to insert, delete, or update.
Simply create the view, and every time you access it, the base tables
involved in the view will be queried. Therefore, the view will always have
the same data as the base tables.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"someone" <nospam@.stopspam.com> wrote in message
news:OusidbIcGHA.3840@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to import data from a source table(table is in Sql) into a view.
> I need to insert, delete or update the view to make sure all rows in the
> view have the same data as the source table. Can you please give me how to
> design this in a Integration Services project?
> Thanks.
>

Can I use SQL Server security with reporting services?

I'm using SQL Server 2000 SP3 Reporting Services SP2
Currently my reports are configured to use a shared data source reference.
The credentials for the data source are configured to use the same specified
user name and password, which is a login I created for the purpose of
generating the reports.
Is there a way to pass a SQL Server account user name and password to the
data source instead of using a shared account?
I've tried creating a Data Source Credentials object, but it didn't work:
dim dsc as new DataSourceCredentials
dsc.DataSourceName = "dsn"
dsc.UserName = "unm"
dsc.Password = "pwd"
...
result = rs.Render (...,dsc,...)
...
Unfortunately, I am prevented from using integrated security.
The report renders fine using the data source reference pre-configured with
the shared username and password.
Thanks!
BillHere is what I do and I think is the best thing to do. Create a readonly SQL
Server user account. Use that for your shared datasource. Limit your access
to reports using the built in security model (based on roles). If a user has
rights to run the report they they see the data. They don't need to use
their SQL Server login to do so. This will prevent you from having to manage
SQL Server accounts as much. And, it is better for performance.
By using a single, read only account it means that all database access is
happening through that account which allows connection pooling to do its
magic. Connection pooling only works if the connection string is identical.
Establishing connections is expensive. If you go the route you are
suggesting (which is not possible out of the box, you would have to write
your own forms based authentication and integrate it) then every time a new
user requests a report there will be a new connection being established to
the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillE" <belgie@.datamti.com> wrote in message
news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
> I'm using SQL Server 2000 SP3 Reporting Services SP2
> Currently my reports are configured to use a shared data source reference.
> The credentials for the data source are configured to use the same
> specified user name and password, which is a login I created for the
> purpose of generating the reports.
> Is there a way to pass a SQL Server account user name and password to the
> data source instead of using a shared account?
> I've tried creating a Data Source Credentials object, but it didn't work:
> dim dsc as new DataSourceCredentials
> dsc.DataSourceName = "dsn"
> dsc.UserName = "unm"
> dsc.Password = "pwd"
> ...
> result = rs.Render (...,dsc,...)
> ...
> Unfortunately, I am prevented from using integrated security.
> The report renders fine using the data source reference pre-configured
> with the shared username and password.
> Thanks!
> Bill
>
>|||Thanks for your response. This is a grey area for me, and I'm having some
trouble with it.
In the model I'm using, the user selects parameters in a web forms
interface, which builds a URL and submits it to the report server. The
report is rendered in the browser.
If the user bookmarks the report, it can be retrieved at any time from the
report server by opening the bookmark. No login is necessary, since the
report uses the shared login.
I don't see a security context to prevent a user without the appropriate
role membership from accessing a report. Perhaps my entire model is flawed.
Thanks
Bill
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
> Here is what I do and I think is the best thing to do. Create a readonly
> SQL Server user account. Use that for your shared datasource. Limit your
> access to reports using the built in security model (based on roles). If a
> user has rights to run the report they they see the data. They don't need
> to use their SQL Server login to do so. This will prevent you from having
> to manage SQL Server accounts as much. And, it is better for performance.
> By using a single, read only account it means that all database access is
> happening through that account which allows connection pooling to do its
> magic. Connection pooling only works if the connection string is
> identical. Establishing connections is expensive. If you go the route you
> are suggesting (which is not possible out of the box, you would have to
> write your own forms based authentication and integrate it) then every
> time a new user requests a report there will be a new connection being
> established to the server.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "BillE" <belgie@.datamti.com> wrote in message
> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to the
>> data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>|||RS always has a security context. Being able to have a report rendered is
different from the security used to actually get the data. For the report to
be rendered RS has to know who the user is. It then uses its role based
security to decide whether to provide the service. Then it renders the
report using the security specified for getting the data. Two levels of
security. Rights to run/render the report, rights to get the data. You have
been thinking on the latter when really the issue is more the former.
You have three choices.
1. Create your own authentication. This allows you to have the user log to
your app using the SQL Server login and have that be the login to your app.
You have to integrate this into Reporting Services.
2. Use integrated security where the windows credentials are used to
authenticate into RS. Then integrated security is use for the data source of
the reports as well.
3. Use integrated security where the windows credentials are used to
authenticate into RS. Then, use a fixed SQL Server account (one or more) in
the credentials tab of shared data source.
I do and recommend #3. #1 is non-trivial. #2 is lower performing and in your
case you say you can't use.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillE" <belgie@.datamti.com> wrote in message
news:uEuGchnCHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Thanks for your response. This is a grey area for me, and I'm having some
> trouble with it.
> In the model I'm using, the user selects parameters in a web forms
> interface, which builds a URL and submits it to the report server. The
> report is rendered in the browser.
> If the user bookmarks the report, it can be retrieved at any time from the
> report server by opening the bookmark. No login is necessary, since the
> report uses the shared login.
> I don't see a security context to prevent a user without the appropriate
> role membership from accessing a report. Perhaps my entire model is
> flawed.
> Thanks
> Bill
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>> Here is what I do and I think is the best thing to do. Create a readonly
>> SQL Server user account. Use that for your shared datasource. Limit your
>> access to reports using the built in security model (based on roles). If
>> a user has rights to run the report they they see the data. They don't
>> need to use their SQL Server login to do so. This will prevent you from
>> having to manage SQL Server accounts as much. And, it is better for
>> performance.
>> By using a single, read only account it means that all database access is
>> happening through that account which allows connection pooling to do its
>> magic. Connection pooling only works if the connection string is
>> identical. Establishing connections is expensive. If you go the route you
>> are suggesting (which is not possible out of the box, you would have to
>> write your own forms based authentication and integrate it) then every
>> time a new user requests a report there will be a new connection being
>> established to the server.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "BillE" <belgie@.datamti.com> wrote in message
>> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to
>> the data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't
>> work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>>
>|||This client has users sharing computers, and they don't want to log off and
log on each time they switch users. However, one user may have access to
some reports which another user doesn't.
I'll have to pursue option #1, or (preferably) convince the client to come
to his senses.
Thanks for your help, I have a clearer understanding of the situation now.
Bill
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%232RmlDoCHHA.4256@.TK2MSFTNGP04.phx.gbl...
> RS always has a security context. Being able to have a report rendered is
> different from the security used to actually get the data. For the report
> to be rendered RS has to know who the user is. It then uses its role based
> security to decide whether to provide the service. Then it renders the
> report using the security specified for getting the data. Two levels of
> security. Rights to run/render the report, rights to get the data. You
> have been thinking on the latter when really the issue is more the former.
> You have three choices.
> 1. Create your own authentication. This allows you to have the user log to
> your app using the SQL Server login and have that be the login to your
> app. You have to integrate this into Reporting Services.
> 2. Use integrated security where the windows credentials are used to
> authenticate into RS. Then integrated security is use for the data source
> of the reports as well.
> 3. Use integrated security where the windows credentials are used to
> authenticate into RS. Then, use a fixed SQL Server account (one or more)
> in the credentials tab of shared data source.
> I do and recommend #3. #1 is non-trivial. #2 is lower performing and in
> your case you say you can't use.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "BillE" <belgie@.datamti.com> wrote in message
> news:uEuGchnCHHA.3228@.TK2MSFTNGP03.phx.gbl...
>> Thanks for your response. This is a grey area for me, and I'm having
>> some trouble with it.
>> In the model I'm using, the user selects parameters in a web forms
>> interface, which builds a URL and submits it to the report server. The
>> report is rendered in the browser.
>> If the user bookmarks the report, it can be retrieved at any time from
>> the report server by opening the bookmark. No login is necessary, since
>> the report uses the shared login.
>> I don't see a security context to prevent a user without the appropriate
>> role membership from accessing a report. Perhaps my entire model is
>> flawed.
>> Thanks
>> Bill
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OhCo0bmCHHA.4892@.TK2MSFTNGP04.phx.gbl...
>> Here is what I do and I think is the best thing to do. Create a readonly
>> SQL Server user account. Use that for your shared datasource. Limit your
>> access to reports using the built in security model (based on roles). If
>> a user has rights to run the report they they see the data. They don't
>> need to use their SQL Server login to do so. This will prevent you from
>> having to manage SQL Server accounts as much. And, it is better for
>> performance.
>> By using a single, read only account it means that all database access
>> is happening through that account which allows connection pooling to do
>> its magic. Connection pooling only works if the connection string is
>> identical. Establishing connections is expensive. If you go the route
>> you are suggesting (which is not possible out of the box, you would have
>> to write your own forms based authentication and integrate it) then
>> every time a new user requests a report there will be a new connection
>> being established to the server.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "BillE" <belgie@.datamti.com> wrote in message
>> news:ercHCCmCHHA.3924@.TK2MSFTNGP02.phx.gbl...
>> I'm using SQL Server 2000 SP3 Reporting Services SP2
>> Currently my reports are configured to use a shared data source
>> reference.
>> The credentials for the data source are configured to use the same
>> specified user name and password, which is a login I created for the
>> purpose of generating the reports.
>> Is there a way to pass a SQL Server account user name and password to
>> the data source instead of using a shared account?
>> I've tried creating a Data Source Credentials object, but it didn't
>> work:
>> dim dsc as new DataSourceCredentials
>> dsc.DataSourceName = "dsn"
>> dsc.UserName = "unm"
>> dsc.Password = "pwd"
>> ...
>> result = rs.Render (...,dsc,...)
>> ...
>> Unfortunately, I am prevented from using integrated security.
>> The report renders fine using the data source reference pre-configured
>> with the shared username and password.
>> Thanks!
>> Bill
>>
>>
>>
>

Can I use Sharepoint (WSS) data in a list as the data source for RS?

I have a non-complex list of about 20 fields in a WSS site. I want to
use the list as the data source for some reports in reporting
services. Can I do that? If so, how? Will the format be correct? Any
help would be much appreciated....This may be more of a question for the Sharepoint group rather than the RS
group. The key is getting the data into a format that RS can recognize, for
example, via an ODBC driver.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Lisa" <budgenlj@.willis.com> wrote in message
news:9abd1432.0412140508.2c95c611@.posting.google.com...
>I have a non-complex list of about 20 fields in a WSS site. I want to
> use the list as the data source for some reports in reporting
> services. Can I do that? If so, how? Will the format be correct? Any
> help would be much appreciated....

Can I use business objects as a data source?

We have decided to use business objects in our new application, which seems to be working well, since they can be used as a binding source for grids, etc.

I am trying to evaluate whether SSIS is a practical solution for our ETL requirements. The problem is, I can't find any examples or references, or even the slightest hint that anyone is using them with business objects. Any attempts to search yield a ton of results which are based on a commercial product called "Business Objects" rather than the design pattern.

It is currently a requirement of our development team that all data access must be done via business objects, rather than communicating directly with the database.

Can anyone provide some more information (besides just suggesting I write a custom connection manager)? Is there anyone who has actually made SSIS work with business objects?

Thanks, Richard

What is a "business object"?|||I don't know what kind of information you're looking for. Yes, it can be done. Yes, you'll have to write custom components to talk to the business objects instead of the database. I've done it for Reporting Services as a custom data extension (source) and it wasn't a bunch of fun. SSIS I think would be even less. Biggest downside I can think of off-hand is that you won't be able to bulk load into the database (think row-by-row), which is going to be really painful in an ETL scenario. I've never heard of an ETL process that wasn't allowed to access the database directly.
|||I'd concur with Jay's assessment. I've done some similar work in the past. Business objects are usually not very efficient for accessing large amounts of data. If your data movement is going to be on an individual row basis, I'm not sure SSIS is the best option for implementing it.|||

We are a 3PL, so in our case, ETL is more along the lines of importing batches of orders and exporting batches of shipping confirmations from and to a variety of data formats. Row-by-row is not an issue.

It would be nice to find a working example of how to do this prior to investing a bunch of time into SSIS.

|||This sounds more like BizTalk than SSIS. It isn't your business objects that I'd be concerned about in SSIS, it's the "variety of data formats". SSIS wants to move 10,000+ rows per second between well-defined sources and destinations and do a bunch of transformations along the way. BizTalk wants to route transactions between different systems and talk different formats to each one.
|||

BizTalk has a price tag. We already have SSIS. Everything I've read to date on it indicates that it is something we should be able to use to meet our needs. I am not questioning whether SSIS is the best choice. I would like to know if anyone has used it successfully with business objects, and if so, can they provide some examples, or other helpful information.

Thanks, Richard.

|||What will your business objects be constructed as? COM objects, web services, .NET assemblies? That has an impact on how easy they are to interface with.|||

SSIS Evaluator wrote:

BizTalk has a price tag. We already have SSIS. Everything I've read to date on it indicates that it is something we should be able to use to meet our needs. I am not questioning whether SSIS is the best choice. I would like to know if anyone has used it successfully with business objects, and if so, can they provide some examples, or other helpful information.

Thanks, Richard.

Can I reiterate Phil's question (above)? What exactly are these "business objects"?

-Jamie

|||

It's a generic term for objects that represent business entities and the rules associated with those entities. So you might have a Customer business object, with all the attributes that a customer has, and methods to represent any operations that can be performed on the customer. Usually, these provide a layer of abstraction between the database and the user interface. Business objects know where their data is stored for persistence, and user interfaces request information from the business objects rather than accessing the database directly. Changes in the data from the UI are also handled through the business object. Data validation, enforcement of business rules, etc, are all handled in the business object.

In the OP's case, evidently they have made an architectural decision that all data access has to go through these business objects. As has been stated, it is possible to do this with SSIS. The degree of complexity depends in large part on how the business objects are implemented. If this is being looked at a method to handle importing or exporting data from business objects to an external database, it might work OK, assuming performance is not the major concern. If it is for business object to business object communication, I really think the OP would be better off investigating other tools.

Can I use a Web Service Method as a Data Source

Can I use a Web Service Method as a Data Source in Report Creation.
Any help will be greatly appreciated.
Vishwanathan RamanYes you can definitely do that. It does involve a bit of work though. You'll
need to built a custom data extension (there's a sample on creating a data
extension for an ADO.NET dataset on MSDN) that will call the web service and
provide the report with the results of the call.
"Vishwanathan Raman" <VishwanathanRaman@.discussions.microsoft.com> wrote in
message news:C7DF2501-D37D-44B0-A1CE-F027741942B4@.microsoft.com...
> Can I use a Web Service Method as a Data Source in Report Creation.
> Any help will be greatly appreciated.
> Vishwanathan Raman
>

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

can I use * to specify 'Output Column' for OLD DB Source Editor?

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

ZZhang wrote:

I am working on a situation similar to 'Get all from Table A that isn't in Table B' http://www.sqlis.com/default.aspx?311

I noticed that if one column's name of source table changes,(say Year to Year2) I have to modify all 'data flow transformations' in the task.

I am new to SSIS.

thanks! -ZZ

You could use '*' if you wanted but this is about as bad as bad practice gets. Don't do it. If the name of a column changes then SSIS will break because it stored the metadata of the external data source. This is by design.

-Jamie

|||

Hi, Jamie,
Thanks so much for your quick response! I have two questions then.

1. How to use * ? I can not find it in the 'OLD DB Source Editor'.

2. Let me simplifing my case. I have a remote source table ( which has may columns, incluing 'ID' and 'Date'). The schema may change, but not 'ID' and 'Date' columns. The DTS job is to get all rows ( select * from myTable where [Date] = getdate() ), and output to a delimited flat file.

What is the best practice SSIS for this case?

Thanks again!

-ZZ

|||

Like I said. You can't do it. If the external metadata changes then your data-flow will error.

-Jamie

|||

thanks, Jamie!

Honestly, this surprised me, if it can not use *. I will choose NOT to use SSIS for my simple job, because it does not make sense to modify ( and test) SSIS package every time the schema changes. I hope there is a workaround to meet my job requirement in SSIS.

-ZZ

|||

What? Your problem is the fact that your schema is changing, not that SSIS can't handle it. Are you saying that its impossible to know what your schema will look like from one day to the next? I've never seen a company that would run its systems like that nor would i want to.

Sorry to sound rude but it just sounds crazy to me!

-Jamie

|||

Thanks Jamie for your time to answer my question!

I am new to SSIS, and have not used variable, expression, and sricpt much. I am open-mind, and believe there is a way (simple or difficult), to solve my issue. Maybe you are right, but here I am searching 'how-to' solution, like ( select * from MyTable). Should I use *? it is another question.

Thanks again!

-ZZhang

|||

Again,

Yes you can use "SELECT * FROM MyTable"|||

It seems that MS has solution for 'Dynamic Metadata', although SSIS pipeline requires static metadata.

"Advacned ETL: Embedding Integration Services" from PDC05 mentioned this issue. SMO is needed.

I am still searching for the samples.

-ZZhang

Can I set Excel connection manager's data source as a variable?

I do not know the Excel file name to load in design time.

Would like to pass the value to a variable in the package in run time?

How to do this?

Thanks,

Guangming

Property Expressions is the feature you want. You can assign and expression to most properties, including the DataSource property of a connection. The expression would just be the name of your variable, e.g.

@.[User:MyVar]

Some UIs expose expressions as a separate tab, or just expand expressions from the normal properties grid.

|||I set ExcelFilePath to a variable:

varExcelFileFullPath.

I set an initial value ( \\test\testShare\aaab.xls ) to it when designing the package. It works

Later I run it inside SQL server as below. It failed.

exec xp_cmdshell 'dtexec /SQL "\NE_LoadExcel_Test_direct" /SERVER ** /USER ***** /PASSWORD ****

/CONNECTION "DataSource-Excel";"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\test\testshare\aaab2.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";"

/CONNECTION "Test.NGAEarth";"Data Source=Test;Initial Catalog=NGAEarth;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.NGAEarth"

/set "\Package.Variables[User::varPreSQLAction].Properties[Value]";"delete from Features where type = ''aaab''"

/set "\Package.Variables[User::varExcelWorkbookName].Properties[Value]";"Sheet1$"

/set "\Package.Variables[User::varDestinationTableName].Properties[Value]";"Features"

/set "\Package.Variables[User::varExcelFileFullPath].Properties[Value]";"\\test\testShare\aaab2.xls"

'

Actually, I tried to change other variables. E.g., I changed the file name from aaab2.xls to aaab.xls, Excel worksheet name from Sheet1$ to Sheet2$.

It seems the package never get the parameters I set as above. It always gets the values I set in design time. The errors I got are:

Error: 2006-02-01 15:54:23.58

Code: 0xC0202009

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: An OLE DB error has occurred. Error code: 0x80040E37.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC02020E8

Source: Extract AdHoc Data from Excel Excel Source [649]

Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

End Error

Error: 2006-02-01 15:54:23.58

Code: 0xC004706B

Source: Extract AdHoc Data from Excel DTS.Pipeline

Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".

End Error

|||

Finally I found the problem:

The way to put the command string is not right after dtexec /SQL.

If there is not ENTER and all commands in one line (as it is a dos command), everything is OK!

Is it COOL!?

Guangming

Can I read from multiple table at run time

Hi,

My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.

Right now I have different Source adapters for each table. I want to reduce the number of source adapters.

Can I

1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).

Issues

a) Most of the table has same schema, but few have different, so all tables which has same schema will work.

For different schema anyway I have to create different data source.

b) Will Destination column mapping work.

If any one has already worked on it let me know.

Thanks

Dharmbir

Hi Dharmbir,

I think you are on the right track and I have succeeded with a similar requirement in the past.

Your variable SQL statement will need to return a consistent list of columns back to SSIS. Eg if your first iteration through the loop ran:

select col_a, col_b from table_1

... but your second iteration was just

select col_a from table_2

... this would fail. To avoid this, rejig your second sql statement along these lines:

select col_a, 'any string' as col_b from table_2

This technique will trick SSIS into thinking it is receiving the same answer set each time. Beware of datatype mismatches.

Good luck.

Mike

Can I read from multiple table at run time

Hi,

My Issue is that, I have to create a package which should read from multiple table from a RDBMS Source.

Right now I have different Source adapters for each table. I want to reduce the number of source adapters.

Can I

1) Create a connection manager and set the connection string from a variable. Set the variable from a script task. Put both of these into for loop container (no of tables).

Issues

a) Most of the table has same schema, but few have different, so all tables which has same schema will work.

For different schema anyway I have to create different data source.

b) Will Destination column mapping work.

If any one has already worked on it let me know.

Thanks

Dharmbir

Hi Dharmbir,

I think you are on the right track and I have succeeded with a similar requirement in the past.

Your variable SQL statement will need to return a consistent list of columns back to SSIS. Eg if your first iteration through the loop ran:

select col_a, col_b from table_1

... but your second iteration was just

select col_a from table_2

... this would fail. To avoid this, rejig your second sql statement along these lines:

select col_a, 'any string' as col_b from table_2

This technique will trick SSIS into thinking it is receiving the same answer set each time. Beware of datatype mismatches.

Good luck.

Mike