Help with CustomComponent in SSIS-DataFlow

Hello Trying to figure out a clever solution for splitting multivalued columns out into n-columns. For that I've build a custom component in SSIS using ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9/html/4dc0f631-8fd6-4007-b573-ca67f58ca068.htm as an example. I need to be able to add columns to the OutputCollection in designtime, but the designer returns an error: Error at Data Flow Task [Uppercase [5910]]: The component "Uppercase" (5910) does not allow setting output column datatype properties. How do I enable the designer to accept designtime changes in the columncollection?  Kind regards

You have to override the method SetOutputColumnDataTypeProperties in your component and implement it like this:

public override void SetOutputColumnDataTypeProperties(int outputID, int outputColumnID, Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType, int length, int precision, int scale, int codePage)

{

IDTSOutputCollection90 outputColl = this.ComponentMetaData.OutputCollection;

IDTSOutput90 output = outputColl.GetObjectByID(outputID);

IDTSOutputColumnCollection90 columnColl = output.OutputColumnCollection;

IDTSOutputColumn90 column = columnColl.GetObjectByID(outputColumnID);

column.SetDataTypeProperties(dataType, length, precision, scale, codePage);

}

Help with custom total in matrix (hopefully easy adjustment)

Hey guys. I have a matrix that is working the way I want it as far as
groupings and data. I am doing a custom total that works for the first row
group with the following code:
Private currentTotal As Double = 0
Function SetCurrentValue(ByVal currentValue As Double) As Double
currentTotal += currentValue
return currentTotal
End Function
Function GetCurrentValue() As Double
return currentTotal
End Function
Function ResetTotal() As Boolean
currentTotal=0
return True
End Function
In my detail I have:
=IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
If I use only the first IIF statement I get the correct values 19, 38 , 57,
76, 95, 114, 133, 152, 171, 190, 209, and 228.
If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
361, 399, 437 in the first row and in the second row I get:
494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
I need to run the reset method in between row groupings to get it to go back
to 19 for the second row (it will be a different starting value eventually,
but for now I need it to reset).
I tried putting the code as a second row grouping and the column subtotal,
but it won't reset in the proper spot. Let me know!
Thanks!,
BJYou can use rownumber with scope/group so that it resets each and everytime
group changes. Provided your 19,38,57 etc.. are fixed values...
Amarnath
"bjkaledas" wrote:
> Hey guys. I have a matrix that is working the way I want it as far as
> groupings and data. I am doing a custom total that works for the first row
> group with the following code:
> Private currentTotal As Double = 0
> Function SetCurrentValue(ByVal currentValue As Double) As Double
> currentTotal += currentValue
> return currentTotal
> End Function
> Function GetCurrentValue() As Double
> return currentTotal
> End Function
> Function ResetTotal() As Boolean
> currentTotal=0
> return True
> End Function
> In my detail I have:
> =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> If I use only the first IIF statement I get the correct values 19, 38 , 57,
> 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> 361, 399, 437 in the first row and in the second row I get:
> 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> I need to run the reset method in between row groupings to get it to go back
> to 19 for the second row (it will be a different starting value eventually,
> but for now I need it to reset).
> I tried putting the code as a second row grouping and the column subtotal,
> but it won't reset in the proper spot. Let me know!
> Thanks!,
> BJ|||Where would I use the rownumber function? Would I keep track of this in my
custom code or when I call the function in my detail textbox?
"Amarnath" wrote:
> You can use rownumber with scope/group so that it resets each and everytime
> group changes. Provided your 19,38,57 etc.. are fixed values...
>
> Amarnath
> "bjkaledas" wrote:
> > Hey guys. I have a matrix that is working the way I want it as far as
> > groupings and data. I am doing a custom total that works for the first row
> > group with the following code:
> >
> > Private currentTotal As Double = 0
> > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > currentTotal += currentValue
> > return currentTotal
> > End Function
> >
> > Function GetCurrentValue() As Double
> > return currentTotal
> > End Function
> >
> > Function ResetTotal() As Boolean
> > currentTotal=0
> > return True
> > End Function
> >
> > In my detail I have:
> >
> > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> >
> > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> >
> > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > 361, 399, 437 in the first row and in the second row I get:
> >
> > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> >
> > I need to run the reset method in between row groupings to get it to go back
> > to 19 for the second row (it will be a different starting value eventually,
> > but for now I need it to reset).
> >
> > I tried putting the code as a second row grouping and the column subtotal,
> > but it won't reset in the proper spot. Let me know!
> >
> > Thanks!,
> >
> > BJ|||You can use in the textbox so that when it displays it resets exactly.
Amarnath
"bjkaledas" wrote:
> Where would I use the rownumber function? Would I keep track of this in my
> custom code or when I call the function in my detail textbox?
> "Amarnath" wrote:
> > You can use rownumber with scope/group so that it resets each and everytime
> > group changes. Provided your 19,38,57 etc.. are fixed values...
> >
> >
> > Amarnath
> >
> > "bjkaledas" wrote:
> >
> > > Hey guys. I have a matrix that is working the way I want it as far as
> > > groupings and data. I am doing a custom total that works for the first row
> > > group with the following code:
> > >
> > > Private currentTotal As Double = 0
> > > Function SetCurrentValue(ByVal currentValue As Double) As Double
> > > currentTotal += currentValue
> > > return currentTotal
> > > End Function
> > >
> > > Function GetCurrentValue() As Double
> > > return currentTotal
> > > End Function
> > >
> > > Function ResetTotal() As Boolean
> > > currentTotal=0
> > > return True
> > > End Function
> > >
> > > In my detail I have:
> > >
> > > =IIf(Fields!WC_GROUP.Value = "007", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value),
> > > IIf(Fields!WC_GROUP.Value = "008", Code.MyFunc(Fields!WC_GROUP.Value,
> > > Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value), Nothing))
> > >
> > > If I use only the first IIF statement I get the correct values 19, 38 , 57,
> > > 76, 95, 114, 133, 152, 171, 190, 209, and 228.
> > >
> > > If I use both IIf statements I get 19, 57, 95, 133, 171, 209, 247, 285, 323,
> > > 361, 399, 437 in the first row and in the second row I get:
> > >
> > > 494, 532, 570, 608, 646, 684, 722, 760, 798, 836, 874, 912
> > >
> > > I need to run the reset method in between row groupings to get it to go back
> > > to 19 for the second row (it will be a different starting value eventually,
> > > but for now I need it to reset).
> > >
> > > I tried putting the code as a second row grouping and the column subtotal,
> > > but it won't reset in the proper spot. Let me know!
> > >
> > > Thanks!,
> > >
> > > BJ

Help with Custom Security Extension and Application Pool Identity

This is an interesting one.
We have implemented a custom security extension that we call from
within an application. We pass in a userid and password into the
LogonUser method, which is then checked against our database.
However, we have a Master database, and then a few other small
databases for different clients, so the extension uses another
supplied value to go to the master database, and lookup which database
it needs to verify the user in.
So, in the RSReportServer.config file, we store the connection string
to the Master DB.
This is used to connect to the Master DB, and then lookup the
connection string for the secondary DB.
The connection string for the secondary DB uses Integrated
Authentication (as our application requires this).
When the Application Pool (in IIS) that ReportServer runs under, is
set to the NetworkUser, we get an error returned from the WebService
(called from within our application), which says that the Network
Service was not authorized to access the secondary DB. Understandable
:)
So, we changed the Application Pool, so that it uses a Domain account
as it's Identity. Now we receive the following error back (and in all
the log files I can find)..
System.Web.Services.Protocols.SoapException: An internal error
occurred on the report server. See the error log for more details.
--> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified. at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo) at
RSManagedCrypto.RSCrypto.ExportPublicKey() at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection() at
Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName) at
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name) at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTranslator
pathTranslator, String userSuppliedPath, String parameterName) at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) -- End of inner
exception stack trace -- at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items)
The Application Pool User is a member of the IIS_WPG group, and has
been granted write access to the Windows\Temp folder, and the MSSQL
folder where RS is installed (and sub folders).
I also tried adding it to the local machine admin group, and it made
no difference.
That user also has DBO access to all the DBs on our database server.
Can anyone help please?
Thanks
RichardWill you send the report server web service log file?
Also, can you verify that the new identity has a user profile on the machine
(the user should have an entry under c:\Documents and Settings)?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Greenwell" <lazygun@.gmail.com> wrote in message
news:42b7583d.0406251224.18af1b8@.posting.google.com...
> This is an interesting one.
> We have implemented a custom security extension that we call from
> within an application. We pass in a userid and password into the
> LogonUser method, which is then checked against our database.
> However, we have a Master database, and then a few other small
> databases for different clients, so the extension uses another
> supplied value to go to the master database, and lookup which database
> it needs to verify the user in.
> So, in the RSReportServer.config file, we store the connection string
> to the Master DB.
> This is used to connect to the Master DB, and then lookup the
> connection string for the secondary DB.
> The connection string for the secondary DB uses Integrated
> Authentication (as our application requires this).
> When the Application Pool (in IIS) that ReportServer runs under, is
> set to the NetworkUser, we get an error returned from the WebService
> (called from within our application), which says that the Network
> Service was not authorized to access the secondary DB. Understandable
> :)
> So, we changed the Application Pool, so that it uses a Domain account
> as it's Identity. Now we receive the following error back (and in all
> the log files I can find)..
> System.Web.Services.Protocols.SoapException: An internal error
> occurred on the report server. See the error log for more details.
> -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified. at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo) at
> RSManagedCrypto.RSCrypto.ExportPublicKey() at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at
Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection() at
> Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName) at
>
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(Stri
ng
> key) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty
(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(Stri
ng
> name) at
Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate) at
> Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTransl
ator
> pathTranslator, String userSuppliedPath, String parameterName) at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) -- End of inner
> exception stack trace -- at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) at
> Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items) -- End of inner exception stack
> trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items)
> The Application Pool User is a member of the IIS_WPG group, and has
> been granted write access to the Windows\Temp folder, and the MSSQL
> folder where RS is installed (and sub folders).
> I also tried adding it to the local machine admin group, and it made
> no difference.
> That user also has DBO access to all the DBs on our database server.
> Can anyone help please?
> Thanks
> Richard|||I have emailed you the log file for this problem.
The user that the application pool runs under does not have an entry
under Docs and Settings, but it is a different user to that which the
Report Server Windows Service runs under, which Does have an entry
under Docs and Settings.
I have never seen an app pool user have a Docs and Settings folder :)
Thank you
Richard
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message news:<OTBYLlYXEHA.748@.TK2MSFTNGP11.phx.gbl>...
> Will you send the report server web service log file?
> Also, can you verify that the new identity has a user profile on the machine
> (the user should have an entry under c:\Documents and Settings)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
<snip>sql

Help using custom VB6 Function in Crystal

I have a function for a date format in VB6 that converts to a Long Integer. I then use the opposing function below to convert to a readable date. Can anyone help me implement the function in Crystal 10.

Public Function fDateLong(plngDate As Long) ' Gets Date from DB format YYYYMMDD (DB date is Long) ,
Dim sDate, sYYYY, sMM, sDD As String
sDate = Trim(CStr(plngDate)) ' trim a Converted Long Date
sYYYY = Left(sDate, 4) ' get YYYY from left
sMM = Mid(sDate, 5, 2) 'get MM from middle
sDD = Right(sDate, 2) ' get DD from right
sDate = sMM & "/" & sDD & "/" & sYYYY ' reassemble
fDateLong = CDate(sDate) ' Convert to Date

End Function

Right now my date is reporting as " 20060612". Any help in implementing this function in Crystal would be appreciated.What is your expected output?|||I would like to take it from the DB format (long) 20060615 to a date format like either June 15 2006 or even 6-15-2006 (or 15-06-2006).

Thanks for you response.|||Create a formula having this code and drag that in the report

Numbervar y:=0;
Numbervar m:=0;
Numbervar da:=0;

y:=Tonumber(left(replace(totext(20060615 ),",",""),4));
m:=Tonumber(mid(replace(totext(20060615 ),",",""),5,2));
da:=Tonumber(mid(replace(totext(20060615 ),",",""),7,2));

monthname(m)+" "+totext(da,0)+ " "+replace(totext(y,0),",","")|||Madhi,
Appreciate the help but I still have no bloody where to put this?
Can you advise?|||As I told you create new formula. Put that code. Save it. Drag it to the details section|||Madhi,

Great stuff; problem solved.

Thanks very much

Help required fro Reporting service webparts

Hi,
Since this is related to sharepoint reporting service web parts, I am
posting in both.
In place of report explorer I need to create my custom explorer. I have
figured out that report explorer implements ICellProvider.
I tried using it in my custom Report explorer, but unfortunately the URL
I am trying to pass is creating problems.
I can see the "report manager" in report viewer web part if my custom
explorer provides the cell data in following form:
"http://server/ReportS?/Users+Folders/domain+user/My
Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
I would appreciate if any one can point out what data (and its format)
Report viewer web part expect as connection parameter.
Thanks
AmitAmit,
Are you using the MS webparts which just has the Viewer and the
explorer?
If so I have some third party web parts that do the parameters etc
which may be an easier solution to your problem.
Kind Regards
Dan Orchard
dan.orchard@.idow.co.uk
Amit Saxena wrote:
> Hi,
> Since this is related to sharepoint reporting service web parts, I am
> posting in both.
> In place of report explorer I need to create my custom explorer. I
> have figured out that report explorer implements ICellProvider. I
> tried using it in my custom Report explorer, but unfortunately the
> URL I am trying to pass is creating problems.
> I can see the "report manager" in report viewer web part if my custom
> explorer provides the cell data in following form:
> "http://server/ReportS?/Users+Folders/domain+user/My
> Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>
> I would appreciate if any one can point out what data (and its
> format) Report viewer web part expect as connection parameter.
>
> Thanks
> Amit|||Hi Dan,
I am using MS webparts. I will appreciate if you can tell me about other
webparts too.
Amit
Dan Orchard wrote:
> Amit,
> Are you using the MS webparts which just has the Viewer and the
> explorer?
> If so I have some third party web parts that do the parameters etc
> which may be an easier solution to your problem.
> Kind Regards
> Dan Orchard
> dan.orchard@.idow.co.uk
>
> Amit Saxena wrote:
>
>>Hi,
>>Since this is related to sharepoint reporting service web parts, I am
>>posting in both.
>>In place of report explorer I need to create my custom explorer. I
>>have figured out that report explorer implements ICellProvider. I
>>tried using it in my custom Report explorer, but unfortunately the
>>URL I am trying to pass is creating problems.
>>I can see the "report manager" in report viewer web part if my custom
>>explorer provides the cell data in following form:
>>"http://server/ReportS?/Users+Folders/domain+user/My
>>Reports/ChangeManagement/Matrix&rs:Command=Renderrc:Area=Report"
>>
>>I would appreciate if any one can point out what data (and its
>>format) Report viewer web part expect as connection parameter.
>>
>>Thanks
>>Amit
>