Custom Task - Custom Property Expression

I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.

I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?

This is what I did in the custom task

customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @.[User::VaraibleName]

Any help on this will be very much appreciated.

Thanks

Are you by chance actually referring to a Custom DataFlow Component? If so, the expressionable properties actually show up on the DataFlow Task container. To edit the expressions, select the DataFlow and open properties. Find the Expressions property and open the Property Expressions Editor. You should see your custom component's expressionable properties in the Property dropdown.

Let me know if this answers your question.

Thanks
Mark

|||Thanks, got it. I tried the same earlier, but did not drag & drop my new component after the change.sql

Custom Stored Procs: Determining source table name

Hello all ...

I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.

It looks like I can get the data values passed as parameters just fine.

I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.

I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.

A handy trick or technique would be helpful!

Thanks!

DB

To answer your question, no the distribution agent does not and cannot pass the table name as a parameter. Since the custom procs are per article, it is expected the table name to be included in the proc.|||

OK, I'll work with that.

Thanks very much!

DB

Custom source adapter UI

I am currently writing a custom source adapter that extracts data from a JD Edwards OneWorld system. In the custom user interface of the source component I need to allow the user to set a query (a custom property), and then refresh a list of output columns that will be extracted based upon that query (similar to the list shown in the advanced editor).

My question is, can I apply the custom property change to the component and build my output column list without closing and restarting my custom UI form? I understand that the IDTSComponentUI interface being implemented allows for transactional editing of the component, in that the changes to the component are not applied until I have returned a result in the implemented Edit() method. However is there a way to apply changes without returning this result (and closing my UI)?

Essentially I am looking to have similar behaviour to that of the 'Refresh' button in the advanced component editor form.

Thanks

The transactional edit work by cloning the component - the UI works only with the component clone, and can use all the functionality of the component. So when the component property changes, your UI should apply the changes to the clone, the component (clone) refreshes it output columns and then UI can show refreshed list of output columns. After user closes the UI, the changes are applied to the original component.|||

great, thanks for the response

Custom Rendering Example.

Does anyone know of any code example available for writing a custom
renderer? Or are there any third party renderers available?
Thanks.
Satish.In case anyone is looking for the same i found one in MSDN mag
http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/
Satish wrote:
> Does anyone know of any code example available for writing a custom
> renderer? Or are there any third party renderers available?
> Thanks.
> Satish.

Custom function with multiple variables

Hello,
I must say I am not too experienced in writing custom functions in
TSQL. Perhaps I am asking impossible
I have run into problems that the client platform is not able to
transmit the unicode characters properly, so I need a solution that
woul allow to call insert/update queries with ASCII charset only. The
current solution is that every unicode string literal is replaced with
a long sum of NCHAR(n) and string literals containing only ASCII chars.
That made me run into some wierd 'query optimisation' error.
I am now thinking maybe I could create a function where I could pass
all the unicode code values and string literals and what would
concatenate all them into one string. So, the question is -
1) Is it possible to create a function with variable number of
arguments
2) Can these arguments be of different types - mixed integer and string
3) Is TSQL strong enough to provide means of creating program that
loops over all the arguments and concatenates all of them into one
string, converting the int arguments uzing NCHAR() function.
Example:
MyFn(256,1000,'ABC',300,400,'1234')
returns ''ABC?' (The funny unicode chars replaced with ? for the
sake of example)
Thank you for any hints,
Pavils> 1) Is it possible to create a function with variable number of arguments ?
No. The number of arguments is defined when you create the function.
Even if you specify default values for some arguments, when you call
the UDF you must specify the DEFAULT keyword in the place of the values
for those arguments.

> 2) Can these arguments be of different types - mixed integer and string ?
No, but in SQL Server 2000 (or later) you can use the sql_variant
datatype, which can represent any other scalar datatype (except text,
ntext, image and timestamp).

> 3) Is TSQL strong enough to [...] ?
Maybe, but I don't think that would be a good idea.
You should think about another way of dealing with this issue.
Razvan

Custom Delivery Extension

Hi,
I'm writing a custom delivery extension that saves the rendered report
to the disk.
My problem is that the output file only contains spaces (the file size
is excatly what it should be but no text only black spaces).
Does anyone know why spaces appear instead of chars ?
this is the code for excel but the same happens with mhtml:
Dim deviceInfo, format As String
format = "EXCEL"
deviceInfo = String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
format)
' Render report
m_files = notification.Report.Render(format, deviceInfo)
Dim fileName As String
fileName = "C:\" & m_files(0).FileName & ".xls"
Dim results(CInt(m_files(0).Data.Length)) As Byte
m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
Dim _filestream As System.IO.FileStream = System.IO.File.OpenWrite(fileName)
_filestream.Write(results, 0, CInt(m_files(0).Data.Length))
_filestream.Flush()
_filestream.Close()You need to take the encoding of the stream into account. Use a stream
reader to read the contents passing in the encoding of the stream, and then
use a stream writer to write to the filestream, also passing in the
encoding.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106326857.837797.301490@.z14g2000cwz.googlegroups.com...
> Hi,
> I'm writing a custom delivery extension that saves the rendered report
> to the disk.
> My problem is that the output file only contains spaces (the file size
> is excatly what it should be but no text only black spaces).
> Does anyone know why spaces appear instead of chars ?
> this is the code for excel but the same happens with mhtml:
> Dim deviceInfo, format As String
> format = "EXCEL"
> deviceInfo => String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> format)
> ' Render report
> m_files = notification.Report.Render(format, deviceInfo)
> Dim fileName As String
> fileName = "C:\" & m_files(0).FileName & ".xls"
> Dim results(CInt(m_files(0).Data.Length)) As Byte
> m_files(0).Data.Read(results, 0, CInt(m_files(0).Data.Length))
> Dim _filestream As System.IO.FileStream => System.IO.File.OpenWrite(fileName)
> _filestream.Write(results, 0, CInt(m_files(0).Data.Length))
> _filestream.Flush()
> _filestream.Close()
>|||Thanks,
I tried what you've suggested but i think i'm doing something wrong
because now i'm getting a zero byte file.
can you provide the code for that?
here's the code i added:
Dim encode As Encoding = m_files(0).Encoding
Dim _readStream As New StreamReader(m_files(0).Data, encode)
Dim _filestream As New System.IO.FileStream(fileName,
FileMode.OpenOrCreate)
Dim _writeStream As New StreamWriter(_filestream, encode)
_writeStream.Write(_readStream.ReadToEnd)
_writeStream.Close()
_readStream.Close()
_FileStream.Close()|||Daniel, somebody,
Please help ...|||You're correct - for excel there's no encoding. I tried the mhtml
format which has ASCIIEncoding.
I'm only sending the format in the device info:
String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
I'm not sure what you mean to pass via url, i've tried to export the
report from the report manager and it works fine.
Can you post how to read it like you do ?|||Try not passing in this device info and instead pass the format in on the
render call only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106853884.827838.300370@.z14g2000cwz.googlegroups.com...
> You're correct - for excel there's no encoding. I tried the mhtml
> format which has ASCIIEncoding.
> I'm only sending the format in the device info:
> String.Format("<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",format)
> I'm not sure what you mean to pass via url, i've tried to export the
> report from the report manager and it works fine.
> Can you post how to read it like you do ?
>|||i tried - notification.Report.Render(format, Nothing), but still
there's a black file.
i checked the findrendersave sample and it works fine there but i can't
use the render method directly from the web service because i need the
subscription info (parameters ...)
any ideas ?|||I'm sorry to say, I don't see what the problem is. I can't see anything
wrong with what you are doing. It should work.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"kman" <kamara1@.yahoo.com> wrote in message
news:1106909217.659089.277880@.z14g2000cwz.googlegroups.com...
>i tried - notification.Report.Render(format, Nothing), but still
> there's a black file.
> i checked the findrendersave sample and it works fine there but i can't
> use the render method directly from the web service because i need the
> subscription info (parameters ...)
> any ideas ?
>|||Hi,
Solved it. It wasn't the encoding after all but the position on the
data stream.
I added this line and it solved it:
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Thanks for your help.
This is the complete code for saving the file (works for excel, mhtml
and pdf):
m_files = notification.Report.Render(format, Nothing)
Dim _fileStream As System.IO.FileStream = Nothing
Dim _renderedOutputFile As RenderedOutputFile
_renderedOutputFile = m_files(0)
Dim fileName As String = "C:\" & _renderedOutputFile.FileName.Trim &
"." & _renderedOutputFile.Extension.Trim
_fileStream = New System.IO.FileStream(fileName, FileMode.OpenOrCreate,
FileAccess.Write)
_renderedOutputFile.Data.Seek(CType(0, Long),
System.IO.SeekOrigin.Begin)
Dim arr(CInt(_renderedOutputFile.Data.Length)) As Byte
_renderedOutputFile.Data.Read(arr, 0,
CInt(_renderedOutputFile.Data.Length))
_fileStream.Write(arr, 0, CInt(_renderedOutputFile.Data.Length))
_fileStream.Close()

Custom data processing extension with dataset

I have been getting to grips with writing a custom data processing extension for using a supplied dataset. I have got it working OK so far, but I don't see how to make use of relational data. There's plenty of code for walking through the fields, but nothing to walk through the tables. Since this clearly has to be possible can someone tell me where I'm going wrong?

Thanks

John Williams.

John,

A CustomReportItem only has one reporting services dataset, which is only a single table, so there are no tables to walk through.

Ian|||

This seems extraordinary! Is there really no way of reporting on more complex data when using a data processing extension?

I suppose theoretically it might be possible to write one dpx for each table, using one table in the main report and one in a sub-report, etc. But when running the reports how would you pass the dataset across ensuring that each dpx received the data that it needed?

We were hoping to use SSRS as a drop in replacement for Crystal Reports, but this is a major blow as it will mean extensive rewriting of our application in order to store the data in to temporary tables in SQL server in order to report on it, and then delete the data afterwards.

|||
Each subreport has its own datasets, separate from the main report's datasets. It is just that you can only have one dataset per data region.

Ian

Custom Data Extension Parameters

I am currently writing a Custom Data Extension using the RS 2000
Microsoft.ReportingServices.Interfaces dll. Iâ've found quite a few examples
online and have almost gotten it to work the way I would like it to. I have
implemented the IDbCommandAnalysis.GetParameters interface and when I create
a report with the report designer (VS 2003) the parameters are auto created
in the data layer and the display layer (what is prompted to the user). The
issue I am running into is that parameter type is not being selected within
the display layer. It remains as the default value of string. Does anyone
know how the designer determines what the type is when it is auto creating
the parameters? Iâ'm pretty sure this is possible since it behaves that way
with the prepackaged Microsoft data extensions. What do I need to implement
to get that to work?
Thanks,
-Nathan F.If you implemented the necessary interfaces, then your parameter class
stores the values of the parameters in an object. This object can then
call getType() later on, which is how I am assuming the designer
determines what type of input it is. In essence you shouldn't need to
add any code to figure out the type of the parameter, but you should
code so that you can figure out what types would go where. Most likely
you will just be placing strings inside your parameters since the
command itself will just be a string. Hope this helps!
Lance M.
Nathan wrote:
> I am currently writing a Custom Data Extension using the RS 2000
> Microsoft.ReportingServices.Interfaces dll. I've found quite a few examples
> online and have almost gotten it to work the way I would like it to. I have
> implemented the IDbCommandAnalysis.GetParameters interface and when I create
> a report with the report designer (VS 2003) the parameters are auto created
> in the data layer and the display layer (what is prompted to the user). The
> issue I am running into is that parameter type is not being selected within
> the display layer. It remains as the default value of string. Does anyone
> know how the designer determines what the type is when it is auto creating
> the parameters? I'm pretty sure this is possible since it behaves that way
> with the prepackaged Microsoft data extensions. What do I need to implement
> to get that to work?
> Thanks,
> -Nathan F.

Custom Assembly Configuration Problems

Hi,
I am attempting to integrate a third party .Net assembly into a custom
report I'm writing. The assembly provides functions for creating 2D barcodes
that are a required format for my solution. I've successfully referenced the
assembly and can run my report from visual studio without error.
However, when I deploy and run the report I get the following exception:
Failed to load expression host assembly. Details:
System.Security.Permissions.SecurityPermission (rsProcessingError)
I've added the Custom Assemblies block to my RSReportDesigner.config and my
RSReportServer.config. I have also added code groups to my
rssrvpolicy.config and rspreviewpolicy.config. I have deployed the
referenced dlls to the appropriate bin directories.
The only thing that I haven't done is code the custom assembly to 'assert
permissions'. I shouldn't have to because the assembly takes a simple string
and returns the encoded string - it doesn't use any 'secure' resources (no
files, db connections, etc.). I have also tried using 'FullTrust'
permissions in my code groups even though that is not recommended. In any
event, I do not have source code for this third party assembly nor license to
modify it if I could.
Is there anything I can do to make this work?
--
PaulThe problem is that the third party assembly has strong name and is not
marked with APTCA
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/aptcatypes.asp)
The report expressions at runtime are located in an untrusted assembly thus
any call to a non_APTCA assembly would fail. What you need to do is to
create a thin wrapper assembly that would accept calls from report
expressions and redirect them into the non-APTCA assembly. You will also
need to give fulltrust to that wrapper assembly
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Keegstra" <PaulKeegstra@.discussions.microsoft.com> wrote in message
news:2F8F0E0C-9C98-4EBD-B9D5-AF6D667975BB@.microsoft.com...
> Hi,
> I am attempting to integrate a third party .Net assembly into a custom
> report I'm writing. The assembly provides functions for creating 2D
> barcodes
> that are a required format for my solution. I've successfully referenced
> the
> assembly and can run my report from visual studio without error.
> However, when I deploy and run the report I get the following exception:
> Failed to load expression host assembly. Details:
> System.Security.Permissions.SecurityPermission (rsProcessingError)
> I've added the Custom Assemblies block to my RSReportDesigner.config and
> my
> RSReportServer.config. I have also added code groups to my
> rssrvpolicy.config and rspreviewpolicy.config. I have deployed the
> referenced dlls to the appropriate bin directories.
> The only thing that I haven't done is code the custom assembly to 'assert
> permissions'. I shouldn't have to because the assembly takes a simple
> string
> and returns the encoded string - it doesn't use any 'secure' resources (no
> files, db connections, etc.). I have also tried using 'FullTrust'
> permissions in my code groups even though that is not recommended. In any
> event, I do not have source code for this third party assembly nor license
> to
> modify it if I could.
> Is there anything I can do to make this work?
> --
> Paul|||That doesn't work. Actually, it makes it worse because whereas before I had
a clearly identified exception, adding the wrapper class only results in my
expression evaluating to #Error - with no clue as to the cause. My guess is
it's just the same error, only now the exception has been wrapped out of view.
--
Paul
"Alexandre Mineev MSFT" wrote:
> The problem is that the third party assembly has strong name and is not
> marked with APTCA
> (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/aptcatypes.asp)
> The report expressions at runtime are located in an untrusted assembly thus
> any call to a non_APTCA assembly would fail. What you need to do is to
> create a thin wrapper assembly that would accept calls from report
> expressions and redirect them into the non-APTCA assembly. You will also
> need to give fulltrust to that wrapper assembly
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Paul Keegstra" <PaulKeegstra@.discussions.microsoft.com> wrote in message
> news:2F8F0E0C-9C98-4EBD-B9D5-AF6D667975BB@.microsoft.com...
> > Hi,
> >
> > I am attempting to integrate a third party .Net assembly into a custom
> > report I'm writing. The assembly provides functions for creating 2D
> > barcodes
> > that are a required format for my solution. I've successfully referenced
> > the
> > assembly and can run my report from visual studio without error.
> >
> > However, when I deploy and run the report I get the following exception:
> > Failed to load expression host assembly. Details:
> > System.Security.Permissions.SecurityPermission (rsProcessingError)
> >
> > I've added the Custom Assemblies block to my RSReportDesigner.config and
> > my
> > RSReportServer.config. I have also added code groups to my
> > rssrvpolicy.config and rspreviewpolicy.config. I have deployed the
> > referenced dlls to the appropriate bin directories.
> >
> > The only thing that I haven't done is code the custom assembly to 'assert
> > permissions'. I shouldn't have to because the assembly takes a simple
> > string
> > and returns the encoded string - it doesn't use any 'secure' resources (no
> > files, db connections, etc.). I have also tried using 'FullTrust'
> > permissions in my code groups even though that is not recommended. In any
> > event, I do not have source code for this third party assembly nor license
> > to
> > modify it if I could.
> >
> > Is there anything I can do to make this work?
> >
> > --
> > Paul
>
>