you can use a script task with C# language with a code like this; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.Method = "POST"; HttpWebResponse response = (HttpWebResponse)request.GetResponse(); reference: http://stackoverflow.com/questions/3000568/hit-external-url-from-code-behindhttp://www.rad.pasfu.com

Hi Guys, For Some reason..I just wanted to Hit the URL(process is like once you hit the URL, the data get updated) I need to do either using SSIS or T-sql please help on it ..Thanks Please let me know in case of questions Thanks a Lot

do you want to fetch data back from that url? or just browse it? could you explain more what you trying to do? http://www.rad.pasfu.com

Just want to hit the URL like open & close. This is dashboard URL..Once I hit the URL and the dashboard data will get refreshed..

you can use a script task with C# language with a code like this; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.Method = "POST"; HttpWebResponse response = (HttpWebResponse)request.GetResponse(); reference: http://stackoverflow.com/questions/3000568/hit-external-url-from-code-behindhttp://www.rad.pasfu.com

Hi Reza I got error like "HttpWebRequest" could not be found . pls help on it string url = "http://testtest.com"; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url); request.Method = "POST"; HttpWebResponse response = (HttpWebResponse)request.GetResponse();

add this statement in the USING parts of script: using System.Net;

Thanks Reza Its working but keep on running(Still script task shows yellow)

you should not remove this part of script task: Dts.TaskResult = ( int)ScriptResults.Success;do you have that line of code available at the end line of your script task's Main function?http://www.rad.pasfu.com

Thanks a Lot Reza ..It's working fine...

I have created the SQL job but its getting failed bcoz of error below please me on it. thanks System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: The operation has timed out at System.Net.HttpWebRequest.GetResponse() at ST_f6553343a7a042d3bdfa103bce1a0b83.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:00:00 PM Finished: 8:01:57 PM Elapsed: 116.844 seconds. The package execution failed. The step failed.

do you have internet connection available from your server? or you have it via proxy? and maybe this is because of permission of SQL Server agent account. http://www.rad.pasfu.com

This is Separate Server Machine which we installed SQL SERVER

This is Separate Server Machine which we installed SQL SERVER and how is the internet connection there? do you have internet connection available on that server? http://www.rad.pasfu.com

No Internet connection on that server

No Internet connection on that server so this means that you can not hit that URL from package, because there is no internet connection there. you have two way to solve that problem: 1- provide internet connection on your SQL Server box 2- create a Intranet webservice on another server which is in your LAN but have access to internet, and copy your current code for hit that url into that webservice. then in SSIS you can call that webservice with WEB SERVICE TASK.http://www.rad.pasfu.com

we provided the internet connection on that machine..but we are getting new issues...I have scheduled it on SQL Agent Job for every one hour But its getting failed due to error like "The operation has timed out"

could you ask these questions please: 1- do you just have that script task in your package? or you have other tasks/components as well? 2- did you provided internet connection straight forward for all users? or for specific users for example for domain users? http://www.rad.pasfu.com

1. Its only script task 2. Internet connection has been provided for all users P.S: If I run the package in BIDS its working fine but its not working in SQL Agent Job Please advise on it

are the machine which you create sql agent job there different than the machine you developed the package on BIDS there? if no, how did you deployed the package to another server?http://www.rad.pasfu.com

No, its in the same server

so maybe this is sql server agent account problem. try to create a Proxy account for the user that you used already to login to the machine. and then run ssis package in sql agent job with that proxy account. this link shows how to create proxy account for ssis: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69441/http://www.rad.pasfu.com

so maybe this is sql server agent account problem. try to create a Proxy account for the user that you used already to login to the machine. and then run ssis package in sql agent job with that proxy account. this link shows how to create proxy account for ssis: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/69441/http://www.rad.pasfu.com

Sure I will check and Let you Know Reza Also, Thanks a lot for your time and help

Hi please review the code below...Still i got the same Operation Timed out error(Normally URL is getting loaded slowly) request.Timeout = 15000 pls confirm this seconds or micro seconds...bcoz this process needs to be wait maximum 15 mins and aslo, confimed I have used in the correct place string url = "URLlink"; WebRequest request = HttpWebRequest.Create(url); WebResponse response = request.GetResponse(); request.Timeout = 15000; StreamReader reader = new StreamReader(response.GetResponseStream()); string urlText = reader.ReadToEnd();

did you tried to raise the value of timeout, what you set already is just 15 seconds, and as you said normarlly that url is getting loaded slowly, and depends on traffic this might take too long, try a bigger numer like 100000 for request (I think default value is 100000, so you may try bigger values) .timeout and let me know the result. http://www.rad.pasfu.com

I have set it to 500000 please let me know the place(coding line, request.Timeout = 500000) which i have is correct or not string url = "URLlink"; WebRequest request = HttpWebRequest.Create(url); WebResponse response = request.GetResponse(); request.Timeout =500000; StreamReader reader = new StreamReader(response.GetResponseStream()); string urlText = reader.ReadToEnd();

I have set it to 500000 please let me know the place(coding line, request.Timeout = 500000) which i have is correct or not string url = "URLlink"; WebRequest request = HttpWebRequest.Create(url); WebResponse response = request.GetResponse(); request.Timeout =500000; StreamReader reader = new StreamReader(response.GetResponseStream()); string urlText = reader.ReadToEnd(); unfortunately I do not have internet on my development box, so I cannot test the code. But I think request.Timeout line should be before request.GetResponse line. and if you get an error let me know what is the error? do you trying to run this through BIDS for now? or from SQL server job?http://www.rad.pasfu.com

Please confirm below code....I was tried in SQL Agent job string url = "URLlink"; WebRequest request = HttpWebRequest.Create(url); request.Timeout =500000; WebResponse response = request.GetResponse(); StreamReader reader = new StreamReader(response.GetResponseStream()); string urlText = reader.ReadToEnd(); Error: 2012-07-31 19:31:41.15 Code: 0x00000001 Source: Hit the URL http gad engba symantec com Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.WebException: The operation has timed out at System.Net.HttpWebRequest.GetResponse() at ST_f6553343a7a042d3bdfa103bce1a0b83.csproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:30:00 PM Finished: 7:31:41 PM Elapsed: 100.293 seconds. The package execution failed. The step failed.

and does it run correctly in BIDS? if yes then your don't need to change the code. did you tried to run sql server job under the proxy account?http://www.rad.pasfu.com

Comments below and does it run correctly in BIDS? if yes then your don't need to change the code. Yes its working fine in BIDS but its not working in SQL Agent Job did you tried to run sql server job under the proxy account? I haven't Tried using proxy account

so, don't change the code. just try to create the proxy account as the link I referred shows, that will solve the problem I think, because problem seems to be in SQL Server agent account.http://www.rad.pasfu.com

so, don't change the code. just try to create the proxy account as the link I referred shows, that will solve the problem I think, because problem seems to be in SQL Server agent account.http://www.rad.pasfu.com

Hi Reza I have created the proxy account and mapped into SQL Agent job If i hit the SQL Job manually it working fine....and I scheduled every one hour lets see hopefully I will keep you post for the same..

Hi Reza I have created the Proxy account and mapped it into SQL Job ...It's working fine...now What is the use of Proxy Account? I really appreciate your help on it...Thanks a Lot...

SQL Server agent jobs usually run as sql server agent service account (which probably is NetworkService or LocalSystem), this account usually do not have access to external resources like shared folder, internet connection .... In this kind of situations we need to run that sql server job as a different user who have enough privilege on external resources, so we create a proxy account for that user and then run sql server job under that proxy account. this means totally that sql server job will be run under that specific windows/domain account. Happy to hear that solved, you are always welcome http://www.rad.pasfu.com

Thanks for your explanation Reza Also, I have one more question here....Earlier the Domain and User name has the access right for the same dashboard application. But I configured like, used the same account as a SQL Server Agent proxy(Which U have advised) and mapped it into SQL Job after that its working fine. Please share your comments on it..

Thanks for your explanation Reza Also, I have one more question here....Earlier the Domain and User name has the access right for the same dashboard application. But I configured like, used the same account as a SQL Server Agent proxy(Which U have advised) and mapped it into SQL Job after that its working fine. Please share your comments on it.. When you run sql server job then the user who logged in into the machine is not the user who run ssis package, but the sql server agent user account is the user who run the package. and probably the NetworkService or LocalSystem user accounts didn't have enough permission to do that stuff. this is the reason of why you need to create the proxy account with an authorized user.http://www.rad.pasfu.com

Thanks for your explanation Reza Also, I have one more question here....Earlier the Domain and User name has the access right for the same dashboard application. But I configured like, used the same account as a SQL Server Agent proxy(Which U have advised) and mapped it into SQL Job after that its working fine. Please share your comments on it.. When you run sql server job then the user who logged in into the machine is not the user who run ssis package, but the sql server agent user account is the user who run the package. and probably the NetworkService or LocalSystem user accounts didn't have enough permission to do that stuff. this is the reason of why you need to create the proxy account with an authorized user.http://www.rad.pasfu.com