Script Transformation

A new question !

When you use a script transformation you have input rows and output rows.

Is it possible to have a different number of rows at input and output ?

Thanks

i found something about it

http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsref9/html/1c3e92c7-a4fa-4fdd-b9ca-ac3069536274.asp|||That's the idea. An asynch component, you need to explicitly add rows to the output buffer, compared to a synchronous transform where you just redirect the input row to the output one for one.|||Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.|||

Coroebus wrote:

Exactly, but the code showed in this help don't match the utilisation in dtsx.

I'm searching a sample source code that make an asynchronous transformation script.

Coroebus,

This is what you want: http://www.sqldts.com/default.aspx?307

-Jamie|||Thanks a lot one more time !|||Please note that Books Online does include a simple sample of each of the 4 types of Script components - source, destination, transformation with synchronous and with asynchronous outputs -- in the section titled "Examples of Specific Types of Script Components ."

-Doug
|||I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

|||

Coroebus wrote:

I get an error with my script :

PipelineBuffer has encountered an invalid row index value.

Dim NbCode As Integer = 20 ' set the nb of code per row
Dim liste As String = Nothing ' the string containing the output row

' We loop for NbCode times
While Not Row.EndOfRowset
For j As Integer = 0 To NbCode
liste = liste & Row.CodeYahoo
If j < NbCode Then liste = liste & ","
If j = NbCode Then
With Output0Buffer
'add a row to the output buffer
.AddRow()
'Set the values of the output buffer column
.Sortie = liste
' Reset Liste value
liste = Nothing
End With
End If
Row.NextRow()
Next
End While

Why are you calling Row.NextRow()? I stand to be corrected but if this is in the Input0_ProcessInputRow() method then you don't need to do this because that method gets called for every input row.

-Jamie

Script Execution of Report with parameters

I need to script the execution of reports with input parameter values and with the report output going to PDF in a local file location on the server. I can't use traditional Report Server scheduling because I have to loop through a list of clients and execute the report for each client.

I appreciate any help you can offer.

Hello,

You should take a look at Data-Driven Subscriptions (if you have Enterprise edition) , it will be able to handle what you're trying to do. You can set the render output, parameters, etc.

Jarret

|||

Thanks for the feedback Jarret. We have only had the Standard edition, but our new server will have the Enterprise edition for me to try Data-Driven Subscriptions in the next couple of weeks.

I setup a test server with the Demo of SQL 2005 Enterprise and here are my results for anyone else having similar goals.

We have about 30-40 reports all of which are used by 40-50 clients. The way I accomplish this is by using dynamic Data Source connections in the reports which can connect to all 40-50 client databases. It works fine for on-demand reporting where I pass in the connection information based on the client calling the report. By using these dynamic Data Sources, it saves me from having to maintain between 1200 and 2000 reports and/or linked reports, one for each combination of report and client. We simply don't have the staff to maintain that many reports.

By following the steps in the BOL and using Credentials stored securely in the report server, you can create a New Data Driven Subscription... and report against multiple databases from one schedule by passing in the connection information from the Subscription database.

Script component as Destination - HasSideEffects?

How to set 'HasSideEffects' property for any input column of a script component used as destination?

My Script component reads input, generates SQL update statements dynamically and fires them. There is no output. When executing, I get this error (which is self-explanatory).

"Script Component" (1573) has been removed from the Data Flow task because its output is not used and its inputs have no side effects. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something.

How do I get around this?

thanks,
Nitesh

Which build do you have and how do you create the components?

I've tested the recent build, when I create a script component
or destination, the input is marked as HasSideEffect = true
(it is property of the input, not particular column AFAIK).

You can check this in Input and Outputs page in component
editor. Select the input, it has property HasSideEffect = true.
It is read-only though, but should have the correct value.|||I had the same problem running on the september CTP.

The "HasSideEffects" property of the input to the script component was not enabled, so I couldn't change it.

A workaround is to create a dummy output for the script component. This output will automatically get HasSideEffects set to True and the component will not be removed.|||I have June CTP.
But, HasSideEffects is a readonly attribute of Input and it is set to false.
I can't change it.

Yeah, for now I am using the work around of using a dummy output.

thanks,
Nitesh|||I tested this in the current build and if you select that the script task is a destination then the input automatically gets HasSideEffects set to true. When you say you had the same problem on the Sept CTP did you create a new script task as a destination or did you open an already existing script task (it won't change since the property is persisted).

Two workarounds:
1. Set the RunInOptimizedMode property on the dataflow task to false. This should stop it from removing component but could impact performance if some other pruning made your package run faster. If nothing else was being pruned then you will notice no perf impact at all.
2. Give your input a strange name (e.g. Fix hassideefects on this input) and then edit the xml directly and search for the strange name and change the HasSideEffects on that input to true. I don't recommended this since editing the xml directly can have serious consequences if done incorrectly but it can be done as a last resort if using option 1 affects your perf too much.

HTH,
Matt