Parameter problem with OLAP and spaces

Hello all,

I have an issue with parameters. Let me explain.....

I have report1 which uses analysis services as it's data source. The report displays a list of our companies sites along with some costs etc. Site names can include spaces and numbers but they are stored in the relational database as varchar[50]. On the relevant text box of report1 I have defined a data detsination using report2 and passing it the site name field.value.

On report2 I have added the site name field as a filter dimension and set it as a parameter. On the report parameters I have set the parameter as being non queried.

When I run report1 and click a value to "drill down" it only works for values that have no spaces and no numbers. If If convert spaces to underscores I get a little further, but sites that have numbers, for example 15_OxfordStreet still do not work. The error message I get is..

"Parser: The syntax for '_Oxford' is incorrect.

Can anyone suggest anything to help?

Many thanks

Matt

You may need to escape the member names. To avoid similar issues, the SSAS team introduced undocumented function UrlEscapeFragment. If you open the sample AW cube, flip to the Actions tab in the Cube Designer, select the Sales Reason Comparision action, and expand the Parameters section, you will see how this function is used to escape the member name.|||

Teo,

Thanks for your reply. I'm still learning SQL 2005 and although I tried your suggestion I could not get it to work. However I did fix the problem. The issue was that I was passing through the "friendly name" of the value, instead of [data1].[field name].&[0] etc. as the jump to parameter!

Arrrrgggg...so simple when you think about it!

M

Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha

param for MySQL data source in SSIS 2005

Hi experts,

I 'm trying to get countNumber from a select statement in MySQL source. Something likes this:

select @.countNumber = count(*) from tableName where condition_1 = xxx

I use ExecuteSQLTask to get the result set with result name @.countNumber along with ADO.NET connection type.
I 've installed mysql-connector-net-5.0.5 cause the source is MySQL 5x.

And I got a task failed error which is described below:

[Execute SQL Task] Error: An error occurred while assigning a value to variable "new_process": "Result binding by name "@.new_process" is not supported for this connection type. ".

Please help me to fix this error, thanks alot

Best reguards,

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

|||

Rick wrote:

Try this and see if it helps,

change the query like, Select count(*) from tableName where condition_1 = xxx

Create a variable of the type object. Make sure in the Execute SQL Task you have selected Result Set as Single Row.

In the ResultSet, give the ResultName as 0 and map it to the variable you have created.

No, do not make the data type of the variable "object." Make it an integer.

|||

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

|||

Rick wrote:

Phil Brammer wrote:

No, do not make the data type of the variable "object." Make it an integer.

Sorry to ask Phil, but can you let me know what is the issue in using the datatype object ?

Sure, the overhead is not needed. Plus, I don't think you can use an object variable in a precedence constraint, for instance (Unless SSIS can implicitly cast it). Object variables are designed to hold more than one row of data and/or multiple columns.|||Thanks Phil |||Hi experts,

At my first try, the task fails with integer data type.
I'm sorry Phil . But you are right when saying that object type is used to store more than one record with many columns.

I try it again with the object data type. It's cool but I have to run ScriptTask to get to cast type of the object var into int32.

Here is the way it runs:
Dim vars As Variables
Dim result As Int32
Dts.VariableDispenser.LockForRead("objVar")
Dts.VariableDispenser.GetVariables(vars)
Try
result = CInt(vars("objVar").Value)
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Dts.Variables("intCount").Value = result

If you have other better ways to solve my problem, it will be appreciated

thanks and best reguards,

khanhmy|||

Have you tried

Define variable 'CountNumber' of type Int32

Query

Select count(*) As CountNumber from TABLENAME where condition_1 = xxxx

In the resultset window map CountNumber to User::CountNumber.

Hope this helps!

|||To back up Jay L's post, this is what I do. Now, I guess it depends on what data type is returned from the COUNT(*) against a MySQL database. Since it's like Oracle, it's probably a DAMN numeric(nn,0) which SSIS has issues with.

You could try:

select cast(count(*) as REAL) from TABLENAME where condition_1 = xxxx

Try stuffing that into a variable of type, double.

Parallel execution of source SQL commands

Hi,

we're accessing a SQL Server as a source for some SSIS packages using quite complex SQL commands. We have dataflows getting data from up to 10 queries. The problem is that SSIS starts all these queries in parallel using up all the memory of the server (the source SQL server, not the server SSIS is running on). So the queries are very slow. Is there any way to force SSIS to start the queries after each other?

I already browsed the web for some answers on that and I'm not very optimistic... Maybe the only solution is really to feed the result of the query in raw files and process them later...

Thanks,

Thomas,

I know you'll already have considered this but what is wrong executing them in sequence using precedence constraints?

Alternatively you could change the package's MaxConcurrentExectuables property which I'm guessing is currently set to -1.

-Jamie

|||

Jamie,

thanks for the fast reply...

As far as I understand (and that's what I see at least while debugging) precendence constraints and MaxConcurrentExecutables control only the workflow, not what's happening in the dataflow... The problem is that it's one dataflow with 10 queries feeding one destination. I guess both options don't help in this case...

|||

Thomas,

I'm sorry, I misunderstood. I thought you meant the queries were all in seperate data-flows. Now I understand your comment about raw files. Currently that is definately the only way to go.

Mind you, if they are all on the same server can you not just join all the tables together using a single SQL statement?

I have raised a similar request on Microsoft Connect about being able to define the order in which we insert to the destination adapters. You can see it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058

Why not click-through and add some detail about your source adapter problem as well? Or open it as a seperate issue. I would definately vote for it.

-Jamie

|||

Hi Jamie,

I hoped that there is a tweak for it... But that's what I expected...

I voted for your suggestion and opened up a new one since the problem and the solution is a little bit different... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178069

THANKS!

|||

Excellent. i've voted and commented on it.

-Jamie

PaperSource

Ciao, I'm using CRAXDDRT automation for printing reports in Crystal Report 10.
I would like to choose the paper source (i.e.: Tray1, Tray2...) for a HP printer.
I use this command:

crystalReport.SelectPrinter "", "", "" TO SET DEFAULT PRINTER
crystalReport.PaperSource = 261 TO SET TRAY1

That printer has 2 trays: if I set for printing on Tray1 but there's no paper into it, tray2 is automatically selected.
I don't want this! I want that a message is prompted in vb code or on the display of hp printer, I want to know that tray1 has no paper and print must be stopped.

How could I do this? Do it depend from the printer type? Is it possible or is it normal that the first tray with paper is used?From what I understand, you can only control the papersource property when the report is not being formatted for printing. So after you open the report, you must set this property before you connect to server and start pumping data into it via the PrintOut method.
Also, the value you should be passing for the upper bin is 1 (crPRBinUpper =1, crPRBinLower = 2), not 261 as your notes show.

dave|||First of all, sorry for my english and thanks for repling me!

With HP Printers upper bin are not good, they need a 'personal code' (261, 262 and so...).
Searching on the internet I've found info about that: after setting the default printer I must pass the tray I need to print.
It works right only if paper is in both of trayes but if a tray is empty it prints automatically using the other one.
Instead of this I would like to know (directly on the printer display or trhrought VB code) if a tray is empty so I can choose to use the other or cancel all.

I hope you understand, but I think there's no solution because I haven't found nothing on the web about this and nobody can help me :(|||Well, if you have a licenced copy of Crystal reports, you had better contact them and ask for a fix.

Dave|||I had a similar problem, passing printer parameters from winspool to an external .net printer appi.

to deal with this in .net, ref. the Crystal 10 CrystalDecisions.CrystalReports.Engine (ver. 10.0.3300.0? assembly)

dont use papersource, this is an enumerated standard.

use CustomPaperSource to set the source from the passed string after the PrinterName is set

code snip:

'Source
Dim m_PrinterSettings As New Printing.PrinterSettings
Dim m_PaperSource As System.Drawing.Printing.PaperSource
Dim m_PrinterSource As Printing.PaperSource
Try
For Each m_PrinterSource In m_PrinterSettings.PaperSources
If m_PrinterSource.SourceName.ToString = <my paper source> Then
m_PaperSource = m_PrinterSource
Exit For
End If
Next

crReportDocument.PrintOptions.CustomPaperSource = m_PaperSource
Catch
'just default to auto if problem
crReportDocument.PrintOptions.PaperSource = CrystalDecisions.[Shared].PaperSource.Auto
End Try

CustomPaperSource is not coded in the 9.x assembly(s)

there is a crystal descision KB article somewhere out there on this.
hope this helps.