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 error

Hello,
I=B4ve done one script where i verify the sql server version and the output return the corresponding collations. First is checked if the version is "SQL Server 7.0" and if it is executes sp_helpsort, if it isn=B4t SQL 7.0 there are a lot of steps and one query where is referenced the serverproperty('collation').
The problem is that im trying this and the following error is returned:
"Server: Msg 195, Level 15, State 10, Line 2
'serverproperty' is not a recognized function name"
but if i comment the query the script runs without any problem.
How is this possible? It should not be possible that this part of the script were checked since the @.@.version of the server is 7.0 and so theres no nedd to check the else statement
I cant understand, down i write a coople of lines used in the script.
if (select substring(@.@.version,1,23)) =3D 'Microsoft SQL Server 7'
begin
exec sp_helpsort end
else
begin
.
.
.
select [Server Name] =3D @.@.servername, [Server Default Collation] =3D convert(char
(50),serverproperty('collation')) end
Hope that you can help me
RegardsThe serverproperty function is only available in 2000 if I'm not mistaken. SP_server_info will return the collation under 7.0.
>--Original Message--
>Hello,
>I=B4ve done one script where i verify the sql server version >and the output return the corresponding collations. First >is checked if the version is "SQL Server 7.0" and if it is >executes sp_helpsort, if it isn=B4t SQL 7.0 there are a lot >of steps and one query where is referenced the >serverproperty('collation').
>The problem is that im trying this and the following error >is returned:
>"Server: Msg 195, Level 15, State 10, Line 2
>'serverproperty' is not a recognized function name"
>but if i comment the query the script runs without any >problem.
>How is this possible? >It should not be possible that this part of the script >were checked since the @.@.version of the server is 7.0 and >so theres no nedd to check the else statement
>I cant understand, down i write a coople of lines used in >the script.
>if (select substring(@.@.version,1,23)) =3D 'Microsoft SQL >Server 7'
> begin
> exec sp_helpsort > end
>else
> begin
> .
> .
> .
> select [Server Name] =3D @.@.servername, > [Server Default Collation] =3D convert(char
>(50),serverproperty('collation')) > end
>Hope that you can help me
>Regards
>.
>

Script Component as Source

I want to use Script Component as Source, but I don't know how to code the output rows. Someone can give me some clue or some sample codes? Thanks in advance.

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

|||Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

|||

jwelch wrote:

Here's a sample

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/22/writing-a-resultset-to-a-flat-file.aspx

Sort of... He's doing some other stuff in there than a basic script component source...

For a basic sample:
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/|||

SSIS_NewMan wrote:

Thanks jwelch, that's very helpful.

But, I have a one more question which is the following:
Actually I have a many tables want to do data transfer from one database into another database. So my programming logical is use Foreach loop to read each table name and send to a variable, inside the loop, I use a data flow which contain two script components, one for source which get output rows from table variable, one for destination which insert data into another database. What do you think about my solution? Do you have another better one?

Thanks for your big help.

The control flow has a transfer objects task. Does that not work for you?|||But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.
|||

SSIS_NewMan wrote:

But I only need to transfer some of tables not all, and before insert into another database I also need to do some data conversion. Do you think the Transfer Objects task can help me to do these? Thanks a lot.

Well, you should be using the OLE DB source and destinations, not scripts. But either way you have a big problem. That is, using one data flow to handle this won't likely work too well unless all of your source tables are exactly the same format and are going to tables that have exactly the same format (among ALL of them)

If SourceTableA has 3 columns, all varchars, and SourceTableB has 4 columns, all integers, SSIS will not be able to handle that.

Changing metadata is not within the scope of SSIS' ability to handle.|||Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.
|||Hi Phil Brammer,
Could you give me some advice about this problem?

Thanks,
|||

SSIS_NewMan wrote:

If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table?

I am afraid the short answer is YES. There have been some success reported on this forum about building the packages pragmatically. You may want to research on that

|||

SSIS_NewMan wrote:

Yes, you are right. That is really a problem which I am trying to fig out. So as your experience, what I should to do? If now I have 10 tables or views (of course, all of the source table's format are different) need to transfer, do I need to create 10 SSIS for each source table? What is your best solution for this problem?

Thanks for your any advice and help.

To echo Rafael, if you're performing data checks/edits when transferring the data, then yes, I believe your best option is to build a data flow for each source table.|||Thanks for your reply. I will try to get the best solution.

Script component - column iteration within

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil Brammer wrote:

I have a synchronous script component and have added 5 fields to the output (field1, field2... field5). Can I iterate those fields?

In sudo language, I'd like to do:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
dim i as integer = 1
while i < 6
row.field[i] = "somevalue: " + i.ToString
end while
end sub

Somehow, I'd like to be able to do that without specifying each field individually:

row.field1 = "somevalue: " + "1"
row.field2 = "somevalue: " + "2"
...

Phil,

This should put you on the right path:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

|||

Question. Why on earth do you want to do this?

|||

Jamie Thomson wrote:

Question. Why on earth do you want to do this?

I'm trying to build an example for a poster from last night...

So the above works great for iterating the columns, now I need to assign a value to the output columns by iterating them.

Code Snippet

While counter < maxSegments
For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

If col.Name.Contains("Segment" + (counter + 1).ToString) Then
' Below I need to take the current "column name" and assign a value to it in the output collection
Row.(col.Name) = myStringArray(counter)
Exit For
End If

Next
counter = counter + 1
End While

I just cut and paste with the indentions and it just works for me!|||

Jamie Thomson wrote:

Code Snippet

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim col As IDTSOutputColumn90

For Each col In Me.ComponentMetaData.OutputCollection(0).OutputColumnCollection

MsgBox(col.Name)

Next

End Sub

-Jamie

P.S. How did you get all the code indented in your post?

How do you get color?

|||

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?|||

Phil Brammer wrote:

JayH wrote:

How do you get color?

Let me guess, Jamie: Internet Explorer?

I just copy and paste as well

I get colour but no indents. Phil gets indents but no colour. Weird.

I'm on Vista and IE7.

-Jamie

P.S. color not colour. Whoops - better not get into that!!!

|||

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.
|||

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.|||

Phil Brammer wrote:

JayH wrote:

Phil Brammer wrote:

Let me guess, Jamie: Internet Explorer?

I think I tried IE, but it didn't do it either.

While I'm asking questions, how do users mark questions as "answered". I only see "Was this post helpful?" with Yes/No buttons. Is that it? I just assumed that only the OP had permission to mark the question as answered, and since I never start threads I just wasn't seeing it.

That's correct. Only the OP and moderators can mark threads as answered.

really? dang. I didn't know that.

|||

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

|||

Rafael Salas wrote:

Jamie Thomson wrote:

really? dang. I didn't know that.

What do you mean? aren't you a moderator? If so, you will see 'Mark as answer' buttom on each post

Yes, I'm a moderator. Hence I have the ability to 'mark as answered'. Hence I didn't know that none-moderators do not have the ability to do this.

-Jamie

|||jaegd has a wonderful example of a script component that does what I'm looking at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

script component

i have 2 source columns testsource and testsource1 and 2 output columns

test and test2

i am passing them through a script component to check if the columns are numeric or not along with some more logic...I am able to get the first column evaluated based on the logic but not the second column

Is it that the script component can only look at one column?

this is the code i wrote

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If IsNumeric(Row.testsource) Then

Row.test = Left(Row.testsource, 4) + CStr("/") + Mid(Row.testsource, 5, 2) + CStr("/") + Right(Row.testsource, 2)

Else

If Row.testsource = "N.A" Then

Row.test = "NULL"

End If

Row.test = "NULL"

End If

If IsNumeric(Row.testsource1) Then

Row.test2 = Left(Row.testsource1, 4) + CStr("/") + Mid(Row.testsource1, 5, 2) + CStr("/") + Right(Row.testsource1, 2)

Else

If Row.testsource1 = "N.A" Then

Row.test2 = "NULL"

End If

Row.test2 = "NULL"

End If

End Sub

End Class

example of run

INPUT

,20070930
,20080331
,20070930
,20071130
,20070930
,
,
,
20070504,20070503
20080331,
,20070930
N.A. ,N.A.
N.A. ,N.A.

OUTPUT

NULL,20070930
NULL,20080331
NULL,20070930
NULL,20071130
NULL,20070930
NULL,
NULL,
NULL,
2007/05/04,20070503
2008/03/31,
NULL,20070930
NULL,N.A.
NULL,N.A.

as you can see the first column gets evaluated based on logic but the second column just gets passed through..

Thanks for any help in advance

smathew

Put a data viewer in the path right before the script component and ensure that you have the correct results that you expect (for instance, you don't have that comma in there). Also, you might want to add a derived column before the script component to trim() each field to get rid of any extra spaces.|||

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

|||

smathew wrote:

i do have a derived column that does the trim, but it still does not evaluate the second column based on the logic..

smathew

The other thing you can do after you've looked at the data viewer, is to add a MsgBox(Row.testcolumn1.ToString) to see what happens.

Screen output vs. print output, SSRS 2000

Hi,
I'm building a report for a client (in SSRS 2000), where I am
experiencing the fact that the output on the screen (ie, what is
rendered by Report Manager) is very different from the print output (or
export output, for that matter).
I am using a list, with a couple of tables (with fixed size) embedded
in it. While shown on screen, the page breaks are (as far as I can
tell) randomly inserted, no matter what I set as properties for Page
Break, Keep Together, etc. The output while exporting to (for example)
PDF is great, and exactly as I want it.
Has anyone experienced a similar problem? If so, have you solved it,
and how?
Thanks,
Jeroen Buisman
InfoReports Business Intelligence B.V.I have read in training material that the output to the screen (HTML) does
not have fixed page bounderies but goes until it hits the end of a group set
for a page break at the end of the group. HTML is not a "page oriented"
output format, while PDF is. This has been my experience.
<jeroen.buisman@.gmail.com> wrote in message
news:1141808034.990107.276690@.i39g2000cwa.googlegroups.com...
> Hi,
> I'm building a report for a client (in SSRS 2000), where I am
> experiencing the fact that the output on the screen (ie, what is
> rendered by Report Manager) is very different from the print output (or
> export output, for that matter).
> I am using a list, with a couple of tables (with fixed size) embedded
> in it. While shown on screen, the page breaks are (as far as I can
> tell) randomly inserted, no matter what I set as properties for Page
> Break, Keep Together, etc. The output while exporting to (for example)
> PDF is great, and exactly as I want it.
> Has anyone experienced a similar problem? If so, have you solved it,
> and how?
> Thanks,
> Jeroen Buisman
> InfoReports Business Intelligence B.V.
>|||Hi,
if you dont mention the margin propert for the report layout then this
problem will come up for the HTML print and not for PDF. so you have to
mention the margin hight,width,top,left in the rdl for the html activex
control to get that property otherwise it will get the default property which
makes the report ugly. You can fix temp by changing the margin property to
low in HTML print active control while viewing.
Bava
"jeroen.buisman@.gmail.com" wrote:
> Hi,
> I'm building a report for a client (in SSRS 2000), where I am
> experiencing the fact that the output on the screen (ie, what is
> rendered by Report Manager) is very different from the print output (or
> export output, for that matter).
> I am using a list, with a couple of tables (with fixed size) embedded
> in it. While shown on screen, the page breaks are (as far as I can
> tell) randomly inserted, no matter what I set as properties for Page
> Break, Keep Together, etc. The output while exporting to (for example)
> PDF is great, and exactly as I want it.
> Has anyone experienced a similar problem? If so, have you solved it,
> and how?
> Thanks,
> Jeroen Buisman
> InfoReports Business Intelligence B.V.
>|||Bava
can you explain this more in detail
Thx|||True, but the page breaks are occurring erraticaly. If it were so that
the HTML page break would only occur after the first group which has
the "Page Break at End" (or something like that) set, the break would
occur on exactly the same spot, right? So my guess is that that's not
the case. Will check it though, when I visit my client again tomorrow.
Thx,
Jeroen|||Hi Bava,
You mean that for the Report object, I should set explicit margins? If
so, that's something I always do with my reports. If that's not wat you
mean: could you please clarify what you mean with "changing the margin
property to low in HTML print active control"?
Thx,
Jeroen|||Hi,
The report rdl has the element like
<Report>
<RightMargin>0.2in</RightMargin>
<LeftMargin>0.2in</LeftMargin>
<TopMargin>0.2in</TopMargin>
</Report>
you have to set explicit for the report otherwise it will take the default
of .5in i think.
The default of 0.5in will not be good for some reports in HTML print control
that is avilable in report manager near the Export link.
You can see the value of the report margin set during the print control
preview mode. You can adjust the margin in this print control preview mode
but it will not be saved to the report or in the setting.
Bava
"Jeroen Buisman" wrote:
> Hi Bava,
> You mean that for the Report object, I should set explicit margins? If
> so, that's something I always do with my reports. If that's not wat you
> mean: could you please clarify what you mean with "changing the margin
> property to low in HTML print active control"?
> Thx,
> Jeroen
>|||Ow ok... however, the problem I'm having is not with printing, in fact,
printing is what goes well. The rendering on screen doesn't work as it
should.