Showing posts with label examples. Show all posts
Showing posts with label examples. Show all posts

Friday, March 23, 2012

Need Example of Source Component with Multiple non-Error Outputs

Can someone please point me to one or more examples of a Source component that has two or more outputs?

I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.

Thanks.

Synchronous or async?

Here's a synchronous one I wrote a while back:

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Here's a different one from Jamie:

http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

|||

A source will always be synchronous, by definition. This is a full source component, not a script.

I do not have a suitable sample I can give away, but I can describe it-

Use ProvideComponentProperties to add two outputs, setting ExternalMetadataColumnCollection.IsUsed to true. I also add a custom property as a marker to identify the purpose of each output later on.

Validate and RMD are much the same as usual, do the work that needs doing. PreExecute I use to get the ID of each output and store it in a suitable variable. Same concept as we do with the column index, and we do that too.

In PrimeOutput the array of output ID values can be compared with the ID values you cached in PreExecute. The index in the outputIDs array matches the index in the buffers array, so you can determine which buffer in that array matches which functional output.

Then just add data as normal, add rows and set column values. Do this to each buffer as required.

Finally be sure to call buffer.SetEndOfRowset() on all buffers in the buffers array.

Any help?

|||

Thanks, that's just about what I'm doing, though my (unusual) source doesn't have external metadata. Does it need to?

Like I said, I'm probably doing something silly. I think my next step is to abstract away the specifics of my source. I'll create a source that just returns 10 rows to one output and 10 rows to the other. If that works, then my silly mistake will be in how I send rows to the second output.

Thanks to all.

|||

You do not need it I guess, but it is usual. It acts as a pattern that can be used to describe any external dataset in SSIS terms. We normally work on a model where you can select one or more columns from the external data source, but it does not have o be all. The metadata acts as the choice and output columns are the selection. It also helps for maintenance and validation of columns, because of the separation layer. Still if you had a very stable closed source it may make sense not to bother. I tend to write more flexible components aimed at a wider market, rather than a specific project component.

You send rows to both outputs in exactly the same way, you get multiple buffers in the array, so just add the row to the correct one. That bit the same as havig one buffer, you just need to work out which buffer is which in the array and reference it.

Good luck.

|||

John Saunders wrote:

Like I said, I'm probably doing something silly.

In fact it was so silly, I didn't even realize it. It was bad test data due to having moved from one development environment to the next. I was chaging one file but testing with another file of the same name in a different directory. Very silly.

|||

DarrenSQLIS wrote:

A source will always be synchronous, by definition.

Err...are you sure? I kinda think a source is always asynchronous cos its creating a new execution tree.

-Jamie

sql

Need Example of Source Component with Multiple non-Error Outputs

Can someone please point me to one or more examples of a Source component that has two or more outputs?

I wrote a source, which works with a single output, but after adding a second output, I see no rows there. I've single-stepped the code in the debugger, and it looks like it should be adding rows to the second output, but the downstream component never sees any.

Thanks.

Synchronous or async?

Here's a synchronous one I wrote a while back:

http://agilebi.com/cs/blogs/jwelch/archive/2007/07/12/processing-a-flat-file-with-header-and-detail-rows.aspx

Here's a different one from Jamie:

http://blogs.conchango.com/jamiethomson/archive/2005/09/05/SSIS-Nugget_3A00_-Multiple-outputs-from-a-synchronous-script-transform.aspx

|||

A source will always be synchronous, by definition. This is a full source component, not a script.

I do not have a suitable sample I can give away, but I can describe it-

Use ProvideComponentProperties to add two outputs, setting ExternalMetadataColumnCollection.IsUsed to true. I also add a custom property as a marker to identify the purpose of each output later on.

Validate and RMD are much the same as usual, do the work that needs doing. PreExecute I use to get the ID of each output and store it in a suitable variable. Same concept as we do with the column index, and we do that too.

In PrimeOutput the array of output ID values can be compared with the ID values you cached in PreExecute. The index in the outputIDs array matches the index in the buffers array, so you can determine which buffer in that array matches which functional output.

Then just add data as normal, add rows and set column values. Do this to each buffer as required.

Finally be sure to call buffer.SetEndOfRowset() on all buffers in the buffers array.

Any help?

|||

Thanks, that's just about what I'm doing, though my (unusual) source doesn't have external metadata. Does it need to?

Like I said, I'm probably doing something silly. I think my next step is to abstract away the specifics of my source. I'll create a source that just returns 10 rows to one output and 10 rows to the other. If that works, then my silly mistake will be in how I send rows to the second output.

Thanks to all.

|||

You do not need it I guess, but it is usual. It acts as a pattern that can be used to describe any external dataset in SSIS terms. We normally work on a model where you can select one or more columns from the external data source, but it does not have o be all. The metadata acts as the choice and output columns are the selection. It also helps for maintenance and validation of columns, because of the separation layer. Still if you had a very stable closed source it may make sense not to bother. I tend to write more flexible components aimed at a wider market, rather than a specific project component.

You send rows to both outputs in exactly the same way, you get multiple buffers in the array, so just add the row to the correct one. That bit the same as havig one buffer, you just need to work out which buffer is which in the array and reference it.

Good luck.

|||

John Saunders wrote:

Like I said, I'm probably doing something silly.

In fact it was so silly, I didn't even realize it. It was bad test data due to having moved from one development environment to the next. I was chaging one file but testing with another file of the same name in a different directory. Very silly.

|||

DarrenSQLIS wrote:

A source will always be synchronous, by definition.

Err...are you sure? I kinda think a source is always asynchronous cos its creating a new execution tree.

-Jamie

Wednesday, March 21, 2012

Need code examples of how to call SSRS reports from winforms app

I'm writing a vb.net winforms app and need to call up reports from SSRS. I
also need to pass in secure parameters into the report and I don't want to
pass the credentials if I don't have to. I was hoping I could call my own
web service where I can pass encrypted data to my web service which would
contain the parameters. then a business object on the server could generate
the credential needed by SSRS and pass both the credentials and decrypted
parameters into the report. Problem is I don't know how to get the report
back to the client. One idea was to render the report on the server side
and then convert its html into a byte stream and pass the byte stream as a
return value for my web service. Then my winform client could convert the
byte stream into an html document. However, I have no idea if this is
possible and need examples for the various parts.
can someone please give me advise (and preferable links to code examples) on
how to solve this problem?
Thanks.
--
moondaddy@.nospam.nospamHello,
The following link might be helpful for you:
http://www.codeproject.com/useritems/SQLRSViewer.asp
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "moondaddy" <moondaddy@.nospam.nospam>
| Subject: Need code examples of how to call SSRS reports from winforms app
| Date: Fri, 29 Apr 2005 00:29:02 -0500
| Lines: 22
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <ujCUetHTFHA.2756@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: adsl-70-240-196-183.dsl.hstntx.swbell.net
70.240.196.183
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42254
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm writing a vb.net winforms app and need to call up reports from SSRS.
I
| also need to pass in secure parameters into the report and I don't want
to
| pass the credentials if I don't have to. I was hoping I could call my
own
| web service where I can pass encrypted data to my web service which would
| contain the parameters. then a business object on the server could
generate
| the credential needed by SSRS and pass both the credentials and decrypted
| parameters into the report. Problem is I don't know how to get the
report
| back to the client. One idea was to render the report on the server side
| and then convert its html into a byte stream and pass the byte stream as
a
| return value for my web service. Then my winform client could convert the
| byte stream into an html document. However, I have no idea if this is
| possible and need examples for the various parts.
|
| can someone please give me advise (and preferable links to code examples)
on
| how to solve this problem?
|
| Thanks.
|
| --
| moondaddy@.nospam.nospam
|
|
|sql

Wednesday, March 7, 2012

Need a little help with Join (Beginner)

Im writing this stored procedure and just need to add one more column to the mix. But i cant recall exactly what to do with joins. I have examples, but they all look alittle different. Anyways, i realized that the column i want to add (its highlighted) Needs to have a join. Here is my code. Can anyone explain to me, how i join this with the rest. IT is located in a table called Budget_Reporting_Group and the column is called Budget_Reporting_Group_Description. As you can see, I have another Table called Salesperson_Purchaser, Im pretty sure these two tables are relational by a Salesperson_Purchaser_ID. How would i do the join? If anything, can you display what it would look like (IE yourDatabase.dbo.TableName as Table_Name ON etc etc) I completely forgot the format.

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_Copy] Script Date: 06/28/2007 09:49:08 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTERPROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY]

(@.Region int=Null)

AS

BEGIN

SELECT

[Item_Ledger_Posting_Datetime] Post_Date

,'SMP' Budget_Type

,'Invoice'as Document_Type

,[Item_Ledger_Document_No] Document_Number

,[Item_Description]+' '+'('+[Item_No]+')' Entry_Description

,ISNULL(RC_STAT.dbo.udf_Correct_Price(Item_No, Item_Ledger_Posting_Datetime,'SALESAMP')*-1*Item_Ledger_Invoiced_Qty,Item_Ledger_Cost_Posted_GL *-1 ) Amount

,-1*[Item_Ledger_Invoiced_Qty] Quantity

,Customer_Name

,'' External_Doc_no

,[Item_Ledger_Sales_Responsible] SR_Code

,[Item_Ledger_Mars_Period_Code] ThePeriod

,[Item_Ledger_Mars_Year] TheYear

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Qry_Item_Ledger_Detail]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON [Item_Ledger_Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

WHERE Region.Region_Key = @.Region

AND [Item_Ledger_Mars_Year] = 2007

UNIONALL

SELECT

[GL_Entry_Posting_DateTime]

,[Budget_Type_Code]

,[Document_Type]

,[GL_Entry_Document_No]

,[GL_Entry_Description]

,[GL_Entry_Amount]

,0 Quantity

,[User_ID]

,[GL_Entry_External_Document_No]

,[Sales_Responsible]

,[Mars_Period_Code]

,[Mars_Year]

,Territory.Name AS Territory_Name

,Region.Region AS Region_Name

,Budget_Reporting_Group.Budget_Reporting_Group_Description

FROM [NavisionReplication].[dbo].[Tbl_GL_entry] GL_entry

INNERJOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Budget_Accounts] Budget_Accounts

ON Budget_Accounts.[GL_Account_No] = GL_entry.[GL_Account]

INNERJOIN NavisionReplication.dbo.Tbl_Salesperson_Purchaser AS Salesperson_Purchaser

ON GL_entry.[Sales_Responsible] = Salesperson_Purchaser.SalesPerson_Purchaser_Code

INNERJOIN RC_DWDB_INSTANCE_1.dbo.Territory AS Territory

ON Territory.Code = Salesperson_Purchaser.Territory_Code

LEFTOUTERJOIN RC_DWDB_INSTANCE_1.dbo.Region AS Region

ON Territory.Region_Key = Region.Region_Key

INNERJOIN NavisionReplication.dbo.Tbl_Budget_Reporting as Budget_Reporting

ON Salesperson_Purchaser.Salesperson_Purchaser_Code=Budget_Reporting.Salesperson_Purchaser_Code

WHERE GL_entry.[Mars_Year] = 2007

END

Code Snippet

INNER JOIN Budget_Reporting_Group

ON BUDGET_REPORTING_GROUP.Salesperson_Purchaser_ID = SALESPERSON_PURCHASER.Salesperson_Purchaser_ID

|||

I still keep getting this error:

sg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.

Msg 208, Level 16, State 1, Procedure PROC_RPT_EXPENSE_DETAIL_T&E_MKT_COPY, Line 7

Invalid object name 'Tbl_Budget_Reporting_Group'.