How to capture out param?

I'd like to execute a stored procedure that needs 5 parameters. One of
them is an OUTPUT parameter. What does the
exec sp_name...
code, executed in QA SQL Server 2000, look like for this? Also, if the
OUTPUT parameter is declared last in the spoc, how can I call it by
name as the first parameter in my exec code?
Thanks,
BrettAssuming you have 5 parameters & the 5th one is an OUTPUT paramter, you can
call the procedure like:
EXEC usp @.p1, @.p2, @.p3, @.p4, @.p5 OUT
SELECT @.p5 ;
I know what you posted is just a sample, but in case hadn't noted, avoid
using sp_ prefix for stored procedures sicen they have certain unfavorable
implications.
Anith

how to call web service from within Script Component ?

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Can you not populate a staging table to be used as such:

- After the data flow completes loading a staging table, you'll have an execute SQL task in the control flow to select the data out of that table and store it in an Object variable.
- Use a foreach loop to loop through that object variable, mapping the various columns (if any) to variables
- Inside the foreach loop will be your Web service task. Each time it is called it will use the current value of the parameter variables
- After the foreach loop, you can add another execute SQL task to truncate the staging table if desired.

Does that make sense? At a high level anyway?|||

I think i got what you're saying. However, there is a lot of data. When we go through the data row by row, we take the row data and do 20 different things (update this and that in the database). There is one thing that depends on the output from the webservice.

So, if we use a staging table, memory could become an issue, maybe ? Also, looks like i'll have to to create Data Flow task "twice". The first Data flow task will read the flat file, do some lookup, etc, and put data into a staging table. Then call Web Service Task in the Control Flow. Now that i have the output from the webservice, i need to pass this big object variable back to a Data Flow task for further processing.

I'm just hoping there is a easy way to call the webservice from Script component.... is there a way ?

thanks

|||

mf915 wrote:

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Hi there,

If you buy Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/sr=8-1/qid=1171045629/ref=pd_bbs_sr_1/102-7891523-4086513?ie=UTF8&s=books) there is a downloadable extra chapter that explains EXACTLY how to do this. Many a time I have considered blogging it but that would effectively be plagiarsing Donald's book and I don't want to do that.

-Jamie

|||thanks ! but the book is not available from rationalpress.com until March :(|||

mf915 wrote:

thanks ! but the book is not available from rationalpress.com until March :(

Don't worry. You can get the beta preview version. It contains the same stuff: http://www.amazon.com/Rational-Scripting-Integration-Services-Preview/dp/1932577211/sr=1-2/qid=1171050141/ref=sr_1_2/102-7891523-4086513?ie=UTF8&s=books

-Jamie

|||

Here's one way to use a web service from within an SSIS script component.

1. From the command line, create a wrapper class for the web service using wsdl.exe

wsdl /language:VB http://ws.strikeiron.com/relauto/iplookup?WSDL /out:IPLookup.vb

2. Create an SSIS dataflow, adding in a script component transform.

3. In the script transform, from the Project menu, use "Add Existing Item.." and pull in the wrapper class generated in step 1. You may need to delete the first first few "garbage" characters, which are the Unicode byte order mark.

4. From the Project Menu again, choose Add Reference, and select both the System.Xml and System.Web.Services assemblies.

5. Reference the class from within the transform.

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

Private ws As DNS = New DNS()

' Wrapper for web service

Private dnsInfo As DNSInfo

' wrapper for Response from web service

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Try

Dim server As String = Row.ServerName

dnsInfo = ws.DNSLookup(server)

ComponentMetaData.FireInformation(0, ComponentMetaData.Name, _

String.Format(" {0} maps to IPv4 {1}", server, dnsInfo.AddressList(0)), _

String.Empty, 0, True)

Catch ex As Exception

ComponentMetaData.FireError(1, ComponentMetaData.Name, _

ex.Message, String.Empty, 0, True)

End Try

End Sub

End Class

|||

Spot on as usual jaegd. This is how Donald describes doing it.

-Jamie

|||

Well, I've been able to sucessfully connect to a web service (in my case a Sharepoint List) in the script component using the method described above, and now things are starting to get messy. I'm wondering if anyone has any advice or a "best practice suggestion" for the following scenario, where I'm taking the XML response of a web service and pushing into the data flow using the "XML Source" component:

1. In the script component, query a web service and get the XML response.

1b. Remove multiple namespaces from the XML response using XSLT, since the XML Source component can't handle them.

2. Get the schema of the web service response using the DataSet class/GetXMLSchema method.

3. Push the XML response data into a variable to be read by the "XML Source" component in the dataflow.

4. Write the inferred XML schema to a file. The file name is set using the System::ExecutionInstanceGUID, which ensures a unique file name linked to the particular package execution.

5. Open up the expressions for the data flow task, and set the XMLSchemaDefinition property for the XML Soruce Component to point to the schema file just written. Because this does file not exist at validation time, the data flow task property DelayValidation must be set to True.

Of course, it would be impossible to even create a data flow without having some sort of "starting point" schema to work with. I'm just trying to construct some sort of framework so I can add or remove an item from the Sharepoint List I'm accessing using via Web Service and it doesn't cause my package to fail all over the place because the schema doesn't exactly match the data.

With this (or perhaps a simpler, easier approach?) the dynamically generated schema can change as I make small changes to my sharepoint lists. I'm wondering if I'm making this too complicated, or if it would just be easier to have a maually-maintained repository of XML schemas somewhere in the filesystem.

|||I would just maintain your own XML schemas, and pass the values in through configurations. I'm not sure if there is a simpler or easier approach to a package that has a dynamic schema. Your destinations will have problems with flucuating schemas as well....and from a brief search on the forum building a package dynamically at runtime may be your only option.

how to call web service from within Script Component ?

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Can you not populate a staging table to be used as such:

- After the data flow completes loading a staging table, you'll have an execute SQL task in the control flow to select the data out of that table and store it in an Object variable.
- Use a foreach loop to loop through that object variable, mapping the various columns (if any) to variables
- Inside the foreach loop will be your Web service task. Each time it is called it will use the current value of the parameter variables
- After the foreach loop, you can add another execute SQL task to truncate the staging table if desired.

Does that make sense? At a high level anyway?|||

I think i got what you're saying. However, there is a lot of data. When we go through the data row by row, we take the row data and do 20 different things (update this and that in the database). There is one thing that depends on the output from the webservice.

So, if we use a staging table, memory could become an issue, maybe ? Also, looks like i'll have to to create Data Flow task "twice". The first Data flow task will read the flat file, do some lookup, etc, and put data into a staging table. Then call Web Service Task in the Control Flow. Now that i have the output from the webservice, i need to pass this big object variable back to a Data Flow task for further processing.

I'm just hoping there is a easy way to call the webservice from Script component.... is there a way ?

thanks

|||

mf915 wrote:

Appreciate if anyone can show me the code to call a web service from Script Component ?

I cannot use the Web Service Task. Because parameters to the webservice are from rows of data inside Data Flow Task.

Thanks !!

Hi there,

If you buy Donald Farmer's book (http://www.amazon.com/Rational-Guide-Extending-Script-Guides/dp/1932577254/sr=8-1/qid=1171045629/ref=pd_bbs_sr_1/102-7891523-4086513?ie=UTF8&s=books) there is a downloadable extra chapter that explains EXACTLY how to do this. Many a time I have considered blogging it but that would effectively be plagiarsing Donald's book and I don't want to do that.

-Jamie

|||thanks ! but the book is not available from rationalpress.com until March :(|||

mf915 wrote:

thanks ! but the book is not available from rationalpress.com until March :(

Don't worry. You can get the beta preview version. It contains the same stuff: http://www.amazon.com/Rational-Scripting-Integration-Services-Preview/dp/1932577211/sr=1-2/qid=1171050141/ref=sr_1_2/102-7891523-4086513?ie=UTF8&s=books

-Jamie

|||

Here's one way to use a web service from within an SSIS script component.

1. From the command line, create a wrapper class for the web service using wsdl.exe

wsdl /language:VB http://ws.strikeiron.com/relauto/iplookup?WSDL /out:IPLookup.vb

2. Create an SSIS dataflow, adding in a script component transform.

3. In the script transform, from the Project menu, use "Add Existing Item.." and pull in the wrapper class generated in step 1. You may need to delete the first first few "garbage" characters, which are the Unicode byte order mark.

4. From the Project Menu again, choose Add Reference, and select both the System.Xml and System.Web.Services assemblies.

5. Reference the class from within the transform.

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

Private ws As DNS = New DNS()

' Wrapper for web service

Private dnsInfo As DNSInfo

' wrapper for Response from web service

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Try

Dim server As String = Row.ServerName

dnsInfo = ws.DNSLookup(server)

ComponentMetaData.FireInformation(0, ComponentMetaData.Name, _

String.Format(" {0} maps to IPv4 {1}", server, dnsInfo.AddressList(0)), _

String.Empty, 0, True)

Catch ex As Exception

ComponentMetaData.FireError(1, ComponentMetaData.Name, _

ex.Message, String.Empty, 0, True)

End Try

End Sub

End Class

|||

Spot on as usual jaegd. This is how Donald describes doing it.

-Jamie

|||

Well, I've been able to sucessfully connect to a web service (in my case a Sharepoint List) in the script component using the method described above, and now things are starting to get messy. I'm wondering if anyone has any advice or a "best practice suggestion" for the following scenario, where I'm taking the XML response of a web service and pushing into the data flow using the "XML Source" component:

1. In the script component, query a web service and get the XML response.

1b. Remove multiple namespaces from the XML response using XSLT, since the XML Source component can't handle them.

2. Get the schema of the web service response using the DataSet class/GetXMLSchema method.

3. Push the XML response data into a variable to be read by the "XML Source" component in the dataflow.

4. Write the inferred XML schema to a file. The file name is set using the System::ExecutionInstanceGUID, which ensures a unique file name linked to the particular package execution.

5. Open up the expressions for the data flow task, and set the XMLSchemaDefinition property for the XML Soruce Component to point to the schema file just written. Because this does file not exist at validation time, the data flow task property DelayValidation must be set to True.

Of course, it would be impossible to even create a data flow without having some sort of "starting point" schema to work with. I'm just trying to construct some sort of framework so I can add or remove an item from the Sharepoint List I'm accessing using via Web Service and it doesn't cause my package to fail all over the place because the schema doesn't exactly match the data.

With this (or perhaps a simpler, easier approach?) the dynamically generated schema can change as I make small changes to my sharepoint lists. I'm wondering if I'm making this too complicated, or if it would just be easier to have a maually-maintained repository of XML schemas somewhere in the filesystem.

|||I would just maintain your own XML schemas, and pass the values in through configurations. I'm not sure if there is a simpler or easier approach to a package that has a dynamic schema. Your destinations will have problems with flucuating schemas as well....and from a brief search on the forum building a package dynamically at runtime may be your only option.

How to call report via url in RSReportHost.exe

I am calling my reports via URL, but it presents the report in IE with the url so the users might change the parameters value in IE addressbar. but i see RSReportHost.exe does'nt show the url at all.

Is it possible to show or call a report via URL using RSReportHost.exe

I am using VS 2003 with SQL RS 2005, i don't have a reportviewer control available to use.

Please help guys.

Thank you fery much.

RSReportHost is a desktop application designed for debugging reports. So, the short answer to your question is no. Moreover, URL access is not a very secure way to request reports. The Report ViewerASP.NET control does a reasonable job to hide the parameter values during reposts but it is not foolproof as well. Regardless of the fact that you don’t use the ASP.NET Report, RS 2005 URL addressability uses it behind the scenes by redirecting the user to the ReportViewer.aspx page. So, one way to mitigate the security risk with parameters is to request the report without parameters and rely on the Report Viewer to handle them. Stricter security requirements may require the web application to handle the parameters on the server side.

|||

Thank you very much Teo.

But VS 2003 does'nt have a reportviewer to use with RS 2005.

Microsoft only released the viewer with vs 2005.

I have a very big project fully developed using vs 2003 and lot of state and federal construction projects are using the tool. and also we have integrated third party controls which specifically uses framework 1.1. i don't have an open option to switch to vs 2005 soon might have to wait until all the migration related issues gets cleared.

my current options are to use render webservices to load reports via pdf streams. which i am using. are there any other methods available using vs 2003 and sql rs 2005 please help.

Thank you once again. i read about you on google a lot.

|||

Just to clarify, I was referring to the server ReportViewer.aspx page which the user is automatically redirected to with URL addressability. So, if the user clicks on a URL link which points to a report (e.g. http://localhost/reportserver?%2fAdventureWorks+Sample+Reports%2fCompany+Sales&rs:Command=Render) , the server will redirect her to that page which uses the ReportViewer ASP.NET Report Viewer control behind the scenes. Therefore, you don’t have to upgrade to VS.NET 2005 at all.

|||

Thks Teo, But the problem with URL is the users can manipulate the url parameters by themselves in the IE address bar. I don't want the users manipulate the url's.

|||

True. You have different options to address this depending on the security risk.

1. If you think that hacking the report URL goes beyond the skills of your users, you may be fine with the ReportViewer parameter hiding.

2. You can use the user identity (User!UserID) to filter the parameter available values and/or report data (row-level security).

3. You can configure the Report Server to be accessable from your application server only and not directly from the end users.

4. You can use a trusted account between your application server and Report Server. All report requests will go under this account and you will secure the report catalog accordingly. These security policies will prevent the end users from requesting reports directly.

How to call and pass parameters to Subreport using AWC.RS.Extensio

I am using AWC.RS.Extension to develop both my main and subreport. The
subreport links to the main report through a main report field.
I can pass the xml to the main report by @.DataSource parameter and it works
fine. I also can call the subreport directly (as if it is the main report)
by passing the xml to it through @.DataSource as well.
I don't know how to code both the main and subreport together and pass
parameters to them through their @.DataSource!
Please help!I'm not familiar with exactly what you are doing here but it seems like
@.Datasource is a report parameter in both the main and subreport. So, put
the subreport in the main report. Right click on the subreport, properties,
parameters and set the parameter for the subreport to the parameter from the
main report. This allows you to chain together a parameter from the main and
pass it on to the subreport.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trancomp" <Trancomp@.discussions.microsoft.com> wrote in message
news:0918BA5D-7456-4B9E-980F-EAECF2A7407F@.microsoft.com...
> I am using AWC.RS.Extension to develop both my main and subreport. The
> subreport links to the main report through a main report field.
> I can pass the xml to the main report by @.DataSource parameter and it
works
> fine. I also can call the subreport directly (as if it is the main
report)
> by passing the xml to it through @.DataSource as well.
> I don't know how to code both the main and subreport together and pass
> parameters to them through their @.DataSource!
> Please help!
>|||Bruce, thanks for your reply, I already know how to do the report and
subreport the 'normal' way just like you described.
I am using the data extension from AWC.RS.Extension , I retrieve all the
data first in my code and convert the data to XML stream and then pass the
XML as datasource to the report and subreport. I know how to call the main
report and pass XML as datasource to it, I just don't know how to code the
call to the subreport and pass data to it as XML.
Thanks,
"Bruce L-C [MVP]" wrote:
> I'm not familiar with exactly what you are doing here but it seems like
> @.Datasource is a report parameter in both the main and subreport. So, put
> the subreport in the main report. Right click on the subreport, properties,
> parameters and set the parameter for the subreport to the parameter from the
> main report. This allows you to chain together a parameter from the main and
> pass it on to the subreport.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Trancomp" <Trancomp@.discussions.microsoft.com> wrote in message
> news:0918BA5D-7456-4B9E-980F-EAECF2A7407F@.microsoft.com...
> > I am using AWC.RS.Extension to develop both my main and subreport. The
> > subreport links to the main report through a main report field.
> >
> > I can pass the xml to the main report by @.DataSource parameter and it
> works
> > fine. I also can call the subreport directly (as if it is the main
> report)
> > by passing the xml to it through @.DataSource as well.
> >
> > I don't know how to code both the main and subreport together and pass
> > parameters to them through their @.DataSource!
> >
> > Please help!
> >
>
>

How to call an oracle stored procedure with parameters

I have an oracle stored procedure that will accept 3 parameters from a report
services.... it seems that I can not find the way to call this stored
procedure since the syntax is not the same as this sp was written in SQL
Server.
Could you help?
ThanksAre you running the MSSQL stored proc or the Oracle one? With a MSSQL 2005
sp, just put spMyProc in the query editor in Business Intelligence Studio.
Then configure the parameters using the Edit Dataset window and the
parameters tab.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"greatdane" <greatdane@.discussions.microsoft.com> wrote in message
news:13C68CDD-C7FC-4B18-BCA4-F8F23AD99AA1@.microsoft.com...
>I have an oracle stored procedure that will accept 3 parameters from a
>report
> services.... it seems that I can not find the way to call this stored
> procedure since the syntax is not the same as this sp was written in SQL
> Server.
> Could you help?
> Thanks|||After just typing the spMyProc, I received the following error. The 3
parameters are inside the oracle stored procedure... I had included them also
in the Parameters' tab.
Report item expressions can only refer to fields within the current data
set scope or, if inside an aggregate, the specified data set scope.
Build complete -- 3 errors, 0 warnings
Why is that?
"Alain Quesnel" wrote:
> Are you running the MSSQL stored proc or the Oracle one? With a MSSQL 2005
> sp, just put spMyProc in the query editor in Business Intelligence Studio.
> Then configure the parameters using the Edit Dataset window and the
> parameters tab.
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "greatdane" <greatdane@.discussions.microsoft.com> wrote in message
> news:13C68CDD-C7FC-4B18-BCA4-F8F23AD99AA1@.microsoft.com...
> >I have an oracle stored procedure that will accept 3 parameters from a
> >report
> > services.... it seems that I can not find the way to call this stored
> > procedure since the syntax is not the same as this sp was written in SQL
> > Server.
> >
> > Could you help?
> > Thanks
>

how to call a VB program from a trigger of SQL Server

Hi,

I have a VB program to sit somethere. I like to call that program with passing two parameters from a trigger on the SQL Server. The parameters are the contents of the table which the trigger is created on.

Is there any way to achieve that?

Thanks a lot!

Regards,

Kevin JinYou need create an external stored procedure.

How to build a table which holds varying numbers of fields?

I'm using a stored procedure with multiple parameters, and depending on
the parameters offered, tables with varying fields are returned.
How can I create a table in the layout window which will accomadate
these variations?
Thanks!Only matrixes have dynamic fields... however
for list or table, create the list or table with ALL of the fields, then
conditionally hide/show the fields at run time using the visibility
attribute..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Balding by the handful" wrote:
> I'm using a stored procedure with multiple parameters, and depending on
> the parameters offered, tables with varying fields are returned.
> How can I create a table in the layout window which will accomadate
> these variations?
> Thanks!
>