How to change query timeout?

I'm reporting from a web service that takes so long to return that Reporting
Services times out... how can I increase the timeout value when querying for
data?
TIA - ekkisDont try to increase the timeout, instead optimize your query or use stored
proc.
Amarnath
"ekkis" wrote:
> I'm reporting from a web service that takes so long to return that Reporting
> Services times out... how can I increase the timeout value when querying for
> data?
> TIA - ekkis|||perhaps I didn't explain myself correctly. I am reporting from a web service
so there are no stored procedures involved and the "query" is simply the
request to fetch data from the web service.
I have no control over the foreign server so I need to increase the timeout
value. How can I do that?

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 ReportViewer Refresh method?

I have a web form with a textbox that contains a parameter for a report defined in the ReportViewer control. I want to refresh the report when the user clicks on my refresh button. I can't find the Refresh method. It works if I click on the reviewer's refresh button, but my users need to see that big button that says Refresh. Any ideas of how to call the refresh method? ThanksFound it.

Me.ReportViewer1.LocalReport.Refresh()

How to call Reporting Service

I am not sure how to do it. Please help.
I am trying to call a report I did in REporting Service 2005. I have a web
form where user will go and make selection parameter liek Employee name,
start and End Date.
How I can pass this parameter from my web Form to Reporting Service
Thanks
TanweerSimplist thing to do is to start with URL integration. If you have VS 2005
there is a reportviewer webform control (and a winform control) that is very
good and I highly recommend using it. I am using the Winform version of the
control. Search on URL in Books OnLine.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tanweer" <Tanweer@.discussions.microsoft.com> wrote in message
news:E58A9D53-7BF6-4E13-B60D-113EB0D2DFEA@.microsoft.com...
>I am not sure how to do it. Please help.
> I am trying to call a report I did in REporting Service 2005. I have a web
> form where user will go and make selection parameter liek Employee name,
> start and End Date.
> How I can pass this parameter from my web Form to Reporting Service
> Thanks
> Tanweer

How to call Integration Services Project from Web UI

I just got done finishing an Integration Services Project (which I have to say was sickening easy!) which does the following:

1) Imports a comma delimited txt file

2) Exports it into a table

3) I do some manipulation and other table creation using SQL

4) Outputs a table to a flat file again

I now need to allow the user to run this process. I'd like to either:

a) Provide them a shortcut that when clicked on their desktop starts the process that I have defined in my Integration Services Project

b) Better yet, create a web U I that has a button they can click on, something that shows the progress in time, and then provides the output file as a downloadable link

I'd like to kno whow to do a & b just in case I decide to do one or the other at the end, I'd like to know how to do both for future reference ?

If the package is on the local machine (as well as SSIS), the easiest solution is to use the object model to load and execute the package. See "Running an Existing Package from a Client Application" in BOL. You can do this from any type of managed application, although you'll have extra permissions issues to iron out in a Web app.

If the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

-Doug

|||

I>>>>f the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

My Integration Services Project is calling the stored proc from within an "Execute SQL Task" module in my project. The whole point her is to take advantage of Integration services and it's workflow, after all why would I start a stored procedure outside of this when it's integrated in my packge?

So essentially, I want ASP.NET web button to fire of the start of my Integration Services package, just the same as I go into VS 2005 and click Play to run it. What command can call my package remotely to run it? How can I determine when it's done so I can show a processing bar on my web app?

I know this is possible, there has to be a way programically to invoke / call your Integration Services Project to run from a web page. That's the whole point in using Integration Services to do the dirty work with this stuff, I just want to be able to call it remotely from a web app in ASP.NET - a button or something that runs a script to run the project wherever it resides.

|||

I'm not certain how my response was misunderstood. My answer was precisely about the "way to programmatically invoke an Integration Services package from a Web page" or any other application.

If the package is local, I'd recommend using the API as described in the topic that I quoted, which takes about 2 lines of code (Load and Execute, as well as variable declarations).

You can also call dtexec.exe. If the package is not local, the normal way to launch it is through SQL Agent, by calling the sp_startjob stored procedure to launch the remote package, after configuring an unscheduled job that runs the package. In code, you would use ADO.NET to launch the stored procedure on the remote server.

-Doug

|||Ok, per your last response, now I understand. I have never done any of that before so I needed a more in depth response. Thanks.|||

No problem. Here's the VB code to launch a local package using the API, in case this wasn't in the RTM version of BOL...

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

pkgLocation = _
"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()

Console.WriteLine(pkgResults.ToString())
Console.ReadKey()

End Sub

End Module

|||I really appreciate it, I didn't really know how to go about it. Thanks a lot!|||I'm also assuming I can run a package that is not local if I just tweak the filepath to use UNC or something?|||

Hi, I thought I had asked this question but don't see it in the topic thread. How do you call a SQL Agent job from a client app to run a SSIS package?

Thanks

|||

Using ADO.Net.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

-Doug

|||

You could also - as I would prefer - use SMO.

here are some hints:
http://www.fits-consulting.de/blog/PermaLink,guid,09a62245-9c7f-4d2a-aee2-7f30bbdee1c6.aspx

and here is the detailed link:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.start.aspx

cheers,
Markus

|||Thanks, the VB example in the article is what I was looking for.

How to call Integration Services Project from Web UI

I just got done finishing an Integration Services Project (which I have to say was sickening easy!) which does the following:

1) Imports a comma delimited txt file

2) Exports it into a table

3) I do some manipulation and other table creation using SQL

4) Outputs a table to a flat file again

I now need to allow the user to run this process. I'd like to either:

a) Provide them a shortcut that when clicked on their desktop starts the process that I have defined in my Integration Services Project

b) Better yet, create a web U I that has a button they can click on, something that shows the progress in time, and then provides the output file as a downloadable link

I'd like to kno whow to do a & b just in case I decide to do one or the other at the end, I'd like to know how to do both for future reference ?

If the package is on the local machine (as well as SSIS), the easiest solution is to use the object model to load and execute the package. See "Running an Existing Package from a Client Application" in BOL. You can do this from any type of managed application, although you'll have extra permissions issues to iron out in a Web app.

If the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

-Doug

|||

I>>>>f the package is not on the local machine (or the Web server), then you should configure an unscheduled SQL Agent job to run the package, and use ADO.NET from your app to launch the sp_startjob stored procedure on the server.

My Integration Services Project is calling the stored proc from within an "Execute SQL Task" module in my project. The whole point her is to take advantage of Integration services and it's workflow, after all why would I start a stored procedure outside of this when it's integrated in my packge?

So essentially, I want ASP.NET web button to fire of the start of my Integration Services package, just the same as I go into VS 2005 and click Play to run it. What command can call my package remotely to run it? How can I determine when it's done so I can show a processing bar on my web app?

I know this is possible, there has to be a way programically to invoke / call your Integration Services Project to run from a web page. That's the whole point in using Integration Services to do the dirty work with this stuff, I just want to be able to call it remotely from a web app in ASP.NET - a button or something that runs a script to run the project wherever it resides.

|||

I'm not certain how my response was misunderstood. My answer was precisely about the "way to programmatically invoke an Integration Services package from a Web page" or any other application.

If the package is local, I'd recommend using the API as described in the topic that I quoted, which takes about 2 lines of code (Load and Execute, as well as variable declarations).

You can also call dtexec.exe. If the package is not local, the normal way to launch it is through SQL Agent, by calling the sp_startjob stored procedure to launch the remote package, after configuring an unscheduled job that runs the package. In code, you would use ADO.NET to launch the stored procedure on the remote server.

-Doug

|||Ok, per your last response, now I understand. I have never done any of that before so I needed a more in depth response. Thanks.|||

No problem. Here's the VB code to launch a local package using the API, in case this wasn't in the RTM version of BOL...

Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

Sub Main()

Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult

pkgLocation = _
"C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx"
pkg = app.LoadPackage(pkgLocation, Nothing)
pkgResults = pkg.Execute()

Console.WriteLine(pkgResults.ToString())
Console.ReadKey()

End Sub

End Module

|||I really appreciate it, I didn't really know how to go about it. Thanks a lot!|||I'm also assuming I can run a package that is not local if I just tweak the filepath to use UNC or something?|||

Hi, I thought I had asked this question but don't see it in the topic thread. How do you call a SQL Agent job from a client app to run a SSIS package?

Thanks

|||

Using ADO.Net.

http://msdn2.microsoft.com/en-us/library/ms403355.aspx

-Doug

|||

You could also - as I would prefer - use SMO.

here are some hints:
http://www.fits-consulting.de/blog/PermaLink,guid,09a62245-9c7f-4d2a-aee2-7f30bbdee1c6.aspx

and here is the detailed link:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.agent.job.start.aspx

cheers,
Markus

|||Thanks, the VB example in the article is what I was looking for.

How to call a stored procedure on MS Sqlserver from web forms and get back output

Hi,

How to call a stored procedure on MS Sqlserver from web forms and get back output

Thanx,

Nishu

Within the blue navigation bar at the top of this website, you will see a link to theASP.NET Tutorials. There you will find a large number of tutorials aboutPerforming Data Access.

While you are new to ASP.NET, you should find that most of your questions will be answered--with code samples--inside the Tutorials. Please keep them in mind.

How to call a stored procedure from a web page with VB

Dear Masters;

How can I call a stored procedure with VB code?

Thanks

Check thishttp://aspalliance.com/673_CodeSnip_Calling_a_Stored_Procedure_from_ASPNET_20

Thanks

|||Thanks:)