Does an embedded XML DataSet have to be hardcoded?

I have an XML DataSource in my report which does not specify a connection
string, because I want to use an embedded XML DataSet. However I would like
the XML DataSet text to be defined using an expression (actually a function
call in my custom assembly). Even though you can define an expression for the
XML DataSet, doing so generates a design time error like this:
"The XmlDP query is invalid. Syntax error at line 1, character 3 of the
ElementPath. (Microsoft.ReportingServices.DataExtensions)"
If I define the DataSet text like this it works fine:
============================================= <Query>
<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>
<XmlData>
<Root>
<Parameter ParamName="Param1" ParamValue="Value1"></Parameter>
<Parameter ParamName="Param2" ParamValue="Value2"></Parameter>
<Parameter ParamName="Param3" ParamValue="Value3"></Parameter>
<Parameter ParamName="Param4" ParamValue="Value4"></Parameter>
<Parameter ParamName="Param5" ParamValue="Value5"></Parameter>
</Root>
</XmlData>
</Query>
=============================================
If I define the DataSet text like this I get the error message:
============================================= = "<Query>"
+ "<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>"
+ "<XmlData>"
+ "<Root>"
+ "<Parameter ParamName=""Param1"" ParamValue=""Value1""></Parameter>"
+ "<Parameter ParamName=""Param2"" ParamValue=""Value2""></Parameter>"
+ "<Parameter ParamName=""Param3"" ParamValue=""Value3""></Parameter>"
+ "<Parameter ParamName=""Param4"" ParamValue=""Value4""></Parameter>"
+ "<Parameter ParamName=""Param5"" ParamValue=""Value5""></Parameter>"
+ "</Root>"
+ "</XmlData>"
+ "</Query>"
=============================================
The above is just an expression that shows the error message. Ideally my
DataSet text will look like this:
=Code.RptLib.ReportParametersXML()
It looks like I am up against a limitation in the product. Can you confirm
that this is a limitation in trhe product (embedded XML DataSets MUST be
hardcoded), or is there a workaround or technique that I am missing?
Thanks!
-- Chris
--
Chris, SSSIHello Chirs,
I have reproduced this issue on my side.
I will do some research on this. I appreciate your patience.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Hi Wei Lu,
I found an interesting thing.
By accident I tried =Code.RptLib.ReportParametersXML instead of
=Code.RptLib.ReportParametersXML() and that worked.
Because my method does not take any parameters, I guess VB.NET does not like
the empty parenthesis and for some reason the Report Designer gives a
misleading error message about this problem.
So I solved my problem and I do not have to hard code the XML. It is coming
from my custom assembly.
However, I have no idea why the expression syntax ="..." does not work. But
since I do not need that approach it is not a problem for me right now.
Still, you might want to pass this "bug" on to the developers of SSRS.
-- Chris
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chirs,
> I have reproduced this issue on my side.
> I will do some research on this. I appreciate your patience.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>

Do you need to be a programmer to use SSIS?

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

If i understand your question correctly, the Script Component may be overkill. You could just drag over the OLE DB Dest and Source. Between the two items in the data flow, you could use a Derrived Column transform or Data Conversion based on what the nuts and bolts of your data conversion. It looks like you just want to do a conversion by your example and you could do that in the Data Conversion transform.

-- Brian

|||

Worf wrote:

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

Absolutely not. You have already done the required transformation in your SQL statement above. If you can do your required transforms in the source then invariably that is what you should do - there's no point in having unnecassary transformations in your data-flow. If you wanted to carry out those same transformations in the data-flow then you would require a Data Conversion component (or possibly the Derived Column component) and there is no "programming" (in the common sense of the word) involved in using those.

-Jamie

|||Good call Jaimie.