sql dwh problem: source

Showing posts with label source. Show all posts

Thursday, March 29, 2012

dynamic resizing of chart in IE window

I've got an ASPX page with four iFrames defined (soft of like a
dashboard).
Each one of the IFrames' source is set to another ASPX page that only
contains a ReportViewer control.
I am trying to make it so that when user dynamically sizes the main
(encompassing) window, it resizes everything contained, including the
Reporting Services Chart. I thought setting the width and height to
100% for the web stuff and to 100pc for the RS Chart would do the
trick, now the chart only displays the entire size of my monitor.
Any thoughts?
I'm not a great web developer, by the way. <grin>
Thanks in advance,
Mark FefermanHi Mark,
There is several way to have the dashboard..
1) instead of using iFrames in asp pages.. you can create a dasboard.rdl
page with four charts with drillthrough to detail reports.. you can avoid the
reportmanager and tools above the report by using a URL based report
execution that way it will display only the dashboard report in the IE. im
currently using this approach..
2)Also instead of using ReportViewer control did you tried using URL based
access to reports in all the 4 iFrames
let me know your thoughts and your progress so far..
Thanks
Bava
"mark.feferman@.gmail.com" wrote:
> I've got an ASPX page with four iFrames defined (soft of like a
> dashboard).
> Each one of the IFrames' source is set to another ASPX page that only
> contains a ReportViewer control.
> I am trying to make it so that when user dynamically sizes the main
> (encompassing) window, it resizes everything contained, including the
> Reporting Services Chart. I thought setting the width and height to
> 100% for the web stuff and to 100pc for the RS Chart would do the
> trick, now the chart only displays the entire size of my monitor.
> Any thoughts?
> I'm not a great web developer, by the way. <grin>
> Thanks in advance,
> Mark Feferman
>

Tuesday, March 27, 2012

dynamic Query - ODBC connectivity

I am trying to create Dynamic Query.Connecting to Sybase database source through ODBC, but I try to pass Paremeter by using @.. It says error. Also If i pass &Parameters!Parametername.Value is also not working.Its not saying error for string values but for numeric it says reror. Also in the Query builder i am not able to give expression by giving = "select some Query".

I fgiven an solution or example would be great. Thanks in advance.

I'm not sure but ... does ODBC support named parameters?
Try using ? as parameter, not a questionmark as prefix but like this:

select *
from bla
where something = ?

|||

ODBC is not supporting naming parameter.

I tried with ? working to pass parameter to my query

It was named as Paramater1 when i preview the report and automatically taking the value as string format.Can we change the description and data type?

But I am trying to use Report parameter. i.e. I am getting the values in combo box if in preview the values i select in the query.

But i am unable to use the Report parametr in the query. could anyone help out passing report parameter to sysbase ODBC query

Thanks Hypo for you valuable respons.

Thursday, March 22, 2012

Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.
Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

|||Everything turned out to be much easier, gentlemen!
As soon as I set in my OLE DB Source Properties
"ValidateExternalData" to False
it starts working.
I guess when your source is a variable it should be this way?

Robert
|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

|||I have a variable [SourceTable] that keeps a table name.
I am using it in my OLE DB Source Data Access Mode.
But with ValidateExternalMetadata=True it didn't work.
so I changed it to ValidateExternalMetadata=False
and it works now.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert
sql

Wednesday, March 21, 2012

Dynamic OLE DB Source and Destination

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.Meta data CANNOT change. You cannot setup an OLE DB source and have columns mapped and then just change the underlying source table. How can you imagine that working?

Search this forum for dynamic source/destinations and you'll get plenty of conversations on this topic.

Bottom line, SSIS relies on meta data. If you change it, how do you expect it to operate?|||

Hi Riga,

To make the data source dynamic you need to make the ConnectionString dynamic.

Hope this helps,

Andy

Robert|||

Robert,

Could you please provide more details about your package? what is exactly variable on it? is the name of the table?

Phil is right, the number and data type of the columns cannot change in a dataflow. So, I guess that is not your case; but I am curious about your specific scenario.

I don't know why Phil says it can not be done.
Even if I hold a table name in a variable
the bottom line is that all the tables have identical schema.

So as long as you set a default value of {SourceTable}
let's say to "Table1" it will work when
{SourceTable} changes to "Table2","Table3" and so on.

Robert

Dynamic modification of data flow objects

My idea is to read in source and destination values from a table and using these values within a ForEach loop dynamically alter the source, destination and mapping on the data flow within the package. My reading on SSIS leads me to believe that these properties are not available for modification at run-time. Has anyone any ideas on how to accomplish this task. I have data in over 200 tables to import every 4 hours so I'd rather have to maintain 1 package rather than 200.

You cannot alter the metadata of the data-flow pipeline. In english, that means you cannot change the names and data-types of the columns, not can you add or remove them.

However, you CAN dynamically set the external sources and destinations. Would this be sufficient for you?

-Jamie

|||Hi Jamie - thanks for the quick reply. I don't think this will be sufficient. The 200 tables are all different - we are replicationg tables from an Oracle 8i ERP database to SQL for reporting and analysis purposes. The metadata on each source-destination combination will be different from the next so this will be a problem. As I see it the only way to accomplish this concept is to dynamically create a new package for each table i.e each iteration of the ForEach loop. Do you agree?|||

OK, you have to create 200 packages. But you only have to create them once.

You are correct that the only other option is to dynamically build the package. That's not much fun, believe me!

-Jamie

|||

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

|||

Peter G D wrote:

Thanks for that. That is disappointing as I was hoping for a more elegant solution than creating 200 separate packages.

200 different requirements means 200 things to build. The complexity is in your requirement. I'm slightly confused how it could be made more elegant. I'd welcome your ideas though.

Peter G D wrote:

If I was so hardheaded to try the dynamic building of the package, any ideas on the system overhead taken to dynamically build a package 200 times versus running 200 pre-built packages?

Interesting one. I don't know is the honest answer but I'd love to know. It depends on alot of things, mainly on the amount of data you're moving. The larger dataset then the less the proportionate time to build the package.

Peter G D wrote:

Also, could you suggest any examples on-line re dynamically building the data flow package using VB script?

No way. You won't be able to do this using VBScript. I don't even think you can do it in the Script Task. You are in custom task territory.

-Jamie

|||

i think you you need to use ado.net to iterate over a lookup table that has the table name, source info, and destination info. for each table, you read the data into a recordset, then insert that data into the destination table. you should also probably use a transaction to rollback everything in the event of an error. all of this can be accomplished in a script task.

hope this helps.

|||

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

|||

Peter G D wrote:

Thanks Duane. I've approached the solution much as you prescribe. I've got a table which has the source info and destination info, I read this into an object variable in the package, then use the object recordset as the basis for the Foreach loop. I thought that I'd be able to dynamically change the source and destination information on the data flow task via a script task, and then rebuild the metadata on the data flow task also using a script task(the tables contain exactly the same column names so I naively thought the metadata could be rebuilt using column name matching). However I'm now pessimistic that this approach is possible.

Correct. You cannot do that.

-Jamie

|||

Peter G D wrote:
I'm a little unclear on your solution. When you say "you read the data into a recordset" do you mean read it into an object variable?. (I don't have a development background so I'm a little slow on these concepts!). Can you point me to any examples using a similar approach?

actually, i rather back away from the recordset solution. a better method would be to use raw files instead (for performance reasons). perhaps you could stage the data as raw xml when pulling it out of the source -- i'm not sure if this is the best way. then, you could load that staged data into the destination.

unfortunately, i don't know of any examples to point you towards. all i can tell you is that this solution requires knowledge of ado.net.

Dynamic Mapping For Source/Destination

Hello,

What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?

Is this possible?

Thanks,

awiora

You can do it, but not in the same package. Packages can't modify themselves while they are running. You can, however, use a package (with some code in it) to generate and call another package. Take a look at this post to see an example.

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

|||

Exactly what I was looking for.

Thank you... Much Appreciated.

Sunday, March 11, 2012

Dynamic Flat File Destination Name!

Hi,

I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.

For Example,

CustID, ProductID, Product Name, Product Description

Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.

It is being done in a single Data Flow Task.

Right now the Property Expression for the File Name is which is not working)

Code Snippet

@.DestFolder + [Data Conversion].ProductID + @.TodaysDate + ".txt"

The ProductIDs are in the ascending order. Any help or guidance?

Thanks

-Leo

You can't do that in one data flow unless you want to split it out to a separate destination (and connection manager) for each product. That might be okay for three or four products, but not if you have a bunch.

What you should do instead is set up a ForEach loop based on a the results of a SELECT DISTINCT ProductId query. Then execute your data flow once for each ProductId, customizing both the Source query and the destination connection manager with your ProductId.
|||

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

|||You might be able to use the Export Column transform. That transform writes out a file per row based on one column containing the filename and another column containing the data.|||

Correct me if I am wrong, Ted, but the Export Column transform is used when you have a binary field in your data flow that you want to persist to a file. That doesn't seem to be the case for Leo's data.

Leo, you can partition the file by rows, but what Jay is suggesting would be simpler and perform better. If you use a For Each Loop, you would get a list of all product IDs, then execute a data flow inside the loop for each product ID. Using expressions, you can alter both the OLE DB Source query and the destination file connection string for each iteration of the loop. You wouldn't ever read a destination file - you would only be writing rows to them. And you would only process each row once.

Here's a few examples of using ForEach loops:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

http://www.sqlis.com/55.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx

and a search that might have some more:

http://search.live.com/results.aspx?q=foreach+&form=QBRE&q1=macro%3Ajamiet.ssis

|||The Export Column should work with text and ntext in addition to binary -- the column containing the file data just needs to be either a character or binary lob column. This is just another option for Leo with a different approach, requiring the data and file columns to be derived, which he might be able to do since he has already derived the filename column in his original post.|||

New Leo wrote:

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

Leo,

Yes you can do this. I've covered this exact problem here:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

You won't be able to do it in a single data-flow tho.

-Jamie

Dynamic filedlength of varchar fields in source

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).

When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.

Is there anyway around this problem?

Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...

Regards Andreas

You cannot dynamically change the metadata in an SSIS package -- it needs to be fixed at design time.

You could try casting the source fields to the size they will be on the destination, and hope that the source fields never exceed this size. Or, you could castthe source fields to DT_TEXT so that length can be variable, but performance might suffer (there is extra processing going on for long-object types like DT_TEXT).

Thanks
Mak

|||

A. Brosten wrote:

I'm using SSIS to import data from a table (SQL) containing varchar fields. The problem is, that those varchar fields are changing over time (sometimes shrinking and sometimes expanding). I.e. from varchar(16) to varchar(20).
When I create my SSIS package, the package seem to store information about the length of each source-field. At runtime, if the field-length is larger then what the package expects an error is thown.
Is there anyway around this problem?
Oh, yeah... My destination fields are a lot wider then the source fields, so the problem is not that the varchar values doesn't fit in my destination table, but that the package expects the source to be smaller...
Regards Andreas

You can change the SSIS package so that the external metadata stored within there is large enough for all eventualities.

-Jamie

Friday, March 9, 2012

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

The source table metadata is known via the SYSOBJECTS and SYSCOLUMNS tables- I can pull the column names, type and lengths from these tables based on the table name. (my goal is pretty simple- pull data from a table in our database, and save it down to a flat file)

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

Basically, I created an SSIS package - in it is a script task that dynamically builds a new package- the package dynamically pulls the source and destinations- dynamically creates the columns and destination connection and voila- it works!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

Dynamic Destination Flat File Loading

Here's what I want to do -

Dynamically load a flat file from a dynamic source table-

Would this be enough to dynamically create the destination flat file? If so, how do I do it?

Thanks

-rob

The only way I can imagine is to build a pice of software dynamically creating an SSIS package... Since the metadata of the pipeline can't be dynamic this might be the only way to do this...|||

This is actually the answer!

Took some work, but the script task was able to do it all!

I used the CreatePackage Sample as a guide! If you install the Microsoft SQL Server Samples it will be found in the following directory after installation:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\CreatePackage Sample\VB

Wednesday, March 7, 2012

Dynamic DataSource

How do I change a DataSource on the fly. Do I make the Data Source and the Catalog values parameters in the report then in code feed the parms in like you would a other report parms or in code just make a new definition then call rs2005.SetDataSourceContents(reportPath, definition);?
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.

For example, if you are using SQL Server 2005 as your data source:
Original: data source=localhost\instanceName; initial catalog=AdventureWorks
Expression Based: ="data source=" + Parameters!P1.value + "; initial catalog=" + Parameters!P2.value

You might need to add quotes if your catalog name has spaces. You can use either parameters or an expression. For, example you might have a function you define in your report that looks up the right database for a given user:
="data source=" + Parameters!P1.value + "; initial catalog=" + Code.LookUpDatabaseForUser(Globals!UserID)

The variations on this theme are endless. You might use a different database if you have a different language to get the right group names, etc.

The thing to note is that the databases all have to have the same schema so that your query works.

Of course, you could then make you query to be expression based... but that's adding a whole lot of complexity and should be considered only if you really need it for your report.

-Lukasz|||Thank You Big Smile |||

do you have a sample for rs2000? Thanks.

|||Expression based connection strings are new in RS 2005.

Thanks
Tudor

Dynamic Database Source Changing

Hi,

I am building a data warehouse for a customer who has systems located in two different countries.

I need to import that data from four seperate databases, which all share the same structure.

To do this i have created 20 packages to import that data from the source database. What i would like to do, is at run time set which database the SSIS package should get its data from.

In sql 2k this was easy with a global variable that was set, then use a dynamic properties task to set the data source.

How can i achieve the same result in SSIS? the data source is an ODBC connection, with the four ODBC connections having similar names, eg ABC_NZ, ABC_AU

Thanks in Advance!

Truby

Use a ForEach Loop Container to loop over your collection of ODBC connection names.

Upon each iteration, set the connection string of the connection manager. This technique is described here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx although in this exampe it talks about using a flat file conneciton manager which is not what you want. The principle is the same though.

-Jamie

|||

Hi Jamie,

Thanks for that, it would be perfect if i could run the database extracts from each system at the same time, but i need to be able to schedule the extracts at different times due to different time zones.

what i really want to be able to do is specify the connection at run time.

eg set variable "datasource" to be "ABC_NZ", and that will point at the ABC_NZ ODBC.

Truby

|||

AHA. You can supply this information on the command-line. use the /SET option of dtexec.exe

And use dtexecui.exe to build the command-line for you.

-Jamie

|||

Hi,

I had a similar problem like you. I recognized that it is helpful to use one or more global variables which hold the infomation about the data source. The variable(s) could be set during runtime (i.e. from a db table) and finally you can dynamically change a connection in the connection manager when you click on the connection, properties, expressions. Under expressions you might use your variables to set up a new connection string dynamically.

Example for OLE DB:

"Data Source="+@.[User::Address]+";User ID="+@.[User::UserID]+";Initial Catalog="+@.[User::CatalogName]+";Provider=SQLOLEDB.1;Password="+@.[User::PWD]+";"

If you are not sure about the structure of your connection strings then have a look under:

http://www.connectionstrings.com/

Hope that helps.

Regards,

Stefan

|||

Hi,

You can create a package configuration file and specify Connections being set dynamically from SQL AGent or a schedule job.

In the Integration serivice screen select package configuration Create a file and select the ODBC connection items as configurible. Remember to copy the .dtsconfig file in the place where ur deloying the package.

Once you have done this.

Create a Schedule job under the steps u select the pakage. After setting the package you can go to the connection tab and then change the datasource and the connection strings to what ever you want and leave it.

Like that you can create multiple scheuler for the same dts package and make it run in different time zones according to your requirment.

Hope this helps a bit

Mani

Dynamic database in Web Service

I have a question about changing the database source dynamically in a Web Service. I have created a Web Service for a Crystal Report using VS .NET 2003. I use Crystal Reports Web Viewer to display the report in an ASP.NET page. My database is an Access database and I have a copy of the report source database for each unique user to the Web page. I read the post on changing the database dynamically but it seems to be changed on the Crystal Reports viewer and not in the Crystal report which would be done in the Web Service. I have tried setting debug stops in the code behind module in the Web Service but the debugger does not seem to reach the breakpoints. Can you offer any advice on modifying the database connections dynamically in the Web Service?I have a similar problem. I can set the viewer db properties when i declare a web service but i cant use report engine object model. Can you tell me how you have accomplished that|||I found it fairly easy to bind to a single database. I merely opened the Crystal Report in Visual Studio .NET. In the Report Design view, I right click on the Database Fields in the Field Explorer and Select "Set Location". I defined an ODBC connection to the database and bind the report to that. Turning the report into a Web Services was easy as well. I added the report to a project that I was using to define Web Services. Then right click on the project and select Publish the report as a Web Service. Visual Studio does all the rest. Unforunately all of this is a design/build time and I haven't figured out how to change the database during runtime.

Hope this helps you.|||thanks for the reply. However that is not my problem. I want to be able to export these reports as well. Exporting code only works from report engine object model and you cant use it from client application if your reports are published as web service. That is my complication. I can set parameters, db changes at runtime when i do web services. Still thanks for the reply|||I'm sorry. I guess I did not understand the question about the the report engine object model. I also am not sure what you are referring to on exporting the reports. I haven't done anything beyond the defaults that you get when Visual Studio creates the Web Service except to connect it to the Crystal Reports Web Viewer in the ASPX pages. What did you mean when you said you can set parameters, db changes at runtime through web services? My main question is how to specify a different database file(Access database) at runtime.|||This is how u do it with web services and a strored procedure as your data source. If you use tables as data source. loop through all the tables and set the propetries of the tables(uid,pwd,dbname, servername)
Hope this helps.
Exporting questions is i have to export the reports in crystal to excel, pdf, word format.That can rbe done using viewer object model and i have to do it in web services but i dont know how to modify the engine in the web service code. I have the same problem you have. U cant debug any code in web services

Dim mytablelogoninfos As New CrystalDecisions.Shared.TableLogOnInfos
Dim mytablelogoninfo As New CrystalDecisions.Shared.TableLogOnInfo
Dim myconnectioninfo As New CrystalDecisions.Shared.ConnectionInfo

With myconnectioninfo
.UserID = "uid"
.ServerName = "servername"
.Password = "pwd"
.DatabaseName = "dbname"
End With

mytablelogoninfo.TableName = "cspAppointmentsReport;1"
With mytablelogoninfo.ConnectionInfo
.UserID = "uid"
.Password = "pwd"
End With
mytablelogoninfo.ConnectionInfo = myconnectioninfo
mytablelogoninfos.Add(mytablelogoninfo)
CrystalReportViewer1.LogOnInfo = mytablelogoninfos
CrystalReportViewer1.ReportSource = New localhost.AppointmentSubReportService|||Now I understand what you mean by exporting and no I haven't tried anything like that yet. Sorry I don't think I can help you.

Thanks for the sample code. I will see if I can make it work for what I'm trying to do. I appreciate the help.

Good luck solving your problem.

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

is it possible to put the data source name/connection string in a config file (like web.config) and have the reports use that from there? What is the standard way of doing this while trying to deploy the reports on different servers?

Thanks

Various approaches for dynamic database connections in RS 2000 are available:
* Use a custom data processing extension
http://msdn.microsoft.com/library/en-us/RSPROG/htm/rsp_prog_extend_dataproc_5c2q.asp
* Use the SOAP API by calling SetDataSourceContents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_2ojd.asp
* Use the linked server functionality of SQL Server; please check this thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If the databases are on the same server, use a dynamic query text (i.e. ="select * from " & Parameters!DatabaseName.Value & "..table")
* If you're just toggling between two or three databases, you can publish the same report 3 times with 3 different names using 3 different data sources and write a main report that shows/hides the correct subreport based on whatever criteria you want.

In addition, native support (expression-based connection strings) is available in RS 2005: Finish the design of the datasets with a constant connection string and make sure everything works. Then, go back to the data tab and open the dataset/data source dialog and change the connection string to be an expression. Use string concatenation to plug in the parameter value. Here is an example of how the RDL would look for a parameter-based connection string:
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>="data source=" & Parameters!ServerName.Value & ";initial catalog=Northwind;"</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

We ended up just copying the stored procedure as the text based query . This of course means that every report that would normally based off of one stored procedure has to be individually modified if we need to add or subtract fields.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

1. Create a report parameter "ConnectionString", make sure that this parameter is listed at the top of the parameter list. <example: two existing parameters BeginDate and EndDate, when you add ConnectionString Parameter, move it to the top of the list>

2. If you are planning to have interactivity with another report either via graphs or navigation, create a calculated field in the data set called ConnectionString and make its expression value the ConnectionString Parameter. Use this field to pass the connection string to your external navigation link.

3. Create a new datasource called DynamicSource, for its connection string use the paramter you created earlier (=Parameters!ConnectionString.value). For the query string you have to paste in sql of your stored procedure.

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

This seems to work for SQL datasources....but what about XML datasources. I have a situation where I am getting data from a website that has data criteria in the request string (unfortunately, this is not exactly a webservice). So, I need to dynamically format the request string that is the ConnectString of the XML datasource.

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

When I simply go to the Data tab to add the ="....." to my existing connection string (so I'm not even trying to use any report parameter at this stage), it still doesn't work as if it wouldn't interpret it correctly... could someone help? Is there a switch somewhere to trigger the data source expression in RS 2005?

Thanks in advance,

Xavier

PS: We run a french version...

|||

Ok, found the problem, for those of you who run into the same problem, (ie : '[BC32017] Comma, ')', or a valid expression continuation expected.'.), you might want to make sure there's no space in your Data Source Name, that was the problem for me...

Good luck!

Xavier

|||Hi,

I would suggest you to go ahead create a datasource through ReportManager.
By this way you can make the Reports more flexible and scalable.
I did it in the same way and it did work out well as it was more flexible.
Do not forget to make the datasource shared one.

Regards,
Vamsi Krishna Korasiga.

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

Thanks

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

Thanks in advance,

Xavier

PS: We run a french version...

|||

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

Thanks

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

Thanks in advance,

Xavier

PS: We run a french version...

|||

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

Thanks

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

Thanks in advance,

Xavier

PS: We run a french version...

|||

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

Thanks

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

Thanks in advance,

Xavier

PS: We run a french version...

|||

Good luck!

Xavier

Dynamic data source name...

Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.

Thanks

<ReportParameters>
<ReportParameter Name="ServerName">
<DataType>String</DataType>
<Prompt>ServerName</Prompt>
</ReportParameter>
</ReportParameters>

You can also check this blog posting: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

|||I can't get the 2005 solution for data source expressions to work if I need to use the stored proc command type. Keeps getting reset to text. Is this a bug?|||

Staceyd,

We ran into the same problem with RS 2005 when trying to use a DynamicSource with a stored procedure with the query type reverting to text.

Yes we ended up with our Dynamic connection string, but we also added to our maintenance headache.

Build your report with the stored procedure as its dataset, make sure it runs the way you want it to, then:

4. Test the report.

If anyone finds another work around where the link to the actual stored procedure can be maintained without it reverting back to text based query, please speak up:)

|||

RS_Trans

thanks for the quick reply! And yes, if anyone has a method to do this without reverting back to text based query it would be appreciated, until then I will use that solution

|||Does it works?|||

OK. found it..

The data source must be report specific data source.

Regards,

Visu

|||

Hi,
I am new to Reporting Services. While browsing through the forums, I came across this posting.

Well I just wanted to know whether the above given solution works or not.

Please let me know.

Regards,
Sudhakara.T.P.

|||

Any thoughts?

|||

I tried the solution with a report specific data source and parametrizinf the connection string. I get the follwing error

'An error occured duing report processing.

Cannot connect to the data source.'

I checked the data base and the server name are correct.

Any pointers please?

|||

Can any one please explain more on the solution for dynamic data source connectivity in reporting services 2000

* Use the SOAP API by calling SetDataSourceContents?

If any one has implemented it, can you please provide a step wise pointer to how to get this done.

Will be of great help!

|||

Hi Friend

Did you able to get further information on SOAP API by calling SetDataSourceContents ? if yes please pass it to me

It would be really nice to have this info..

|||

Hi,

Can't seem to get the dynamic data source name to work either, yet we have a brand new SQL Server 2005 installation running with Reporting Services.

Thanks in advance,

Xavier

PS: We run a french version...

|||

Good luck!

Xavier

Thursday, March 29, 2012

dynamic resizing of chart in IE window

Tuesday, March 27, 2012

dynamic Query - ODBC connectivity

Thursday, March 22, 2012

Dynamic OLE DB Source and Destination

Wednesday, March 21, 2012

Dynamic OLE DB Source and Destination

Dynamic modification of data flow objects

Dynamic Mapping For Source/Destination

Sunday, March 11, 2012

Dynamic Flat File Destination Name!

Dynamic filedlength of varchar fields in source

Friday, March 9, 2012

Dynamic Destination Flat File Loading

Dynamic Destination Flat File Loading

Dynamic Destination Flat File Loading

Wednesday, March 7, 2012

Dynamic DataSource

Dynamic Database Source Changing

Dynamic database in Web Service

Dynamic data source name...

Dynamic data source name...

Dynamic data source name...

Dynamic data source name...

Dynamic data source name...

Dynamic data source name...

sql dwh problem

Blog Archive

About Me