SSIS SQL 2005 Script Task For Windows Monitoring Service
May 26, 2006
Hi
Does any body knows how to convert this VBScript Code to VBDotNet (SQL SERVER 2005 INTEGERATION). Below Codes Returns a list of all the services installed on a computer, and indicates their current status (typically, running or not running).Its in VBScript Format.
I have been struggling with a problem with the Web Service Task. I have a package that uses this and has to authenticate to the web service. This works fine on my development server but if I export the package to file and run from another computer / user, the package fails as it doesn't log in to the web service. I have found the problem to be the package was set to default of EncryptSensitiveWithUserKey and I understand why this is. If I change to use EncryptSensitiveWithPassword, it prompts for creds and works fine. My question is how do I use a package configuration file to use different creds (there is not password option for the http connection) so I can standardize on use of config files for this operation. Thanks.
Need some help... When we tried to run mulitple packages one after the other from a windows service, first one succeeds but later ones are throwing below error : "The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. A deadlock was detected while trying to lock variable "System:ackageName, User::BusinessDate, User::Environment, User:ortfolioName" for read access. A lock could not be acquired after 16 attempts and timed out."
Later, we tried to create separate AppDomains for each package and execute via console application, but ended up with below error (The below expressions were defined in OnError Event) : "The result of the expression "@[User::ReportErrorFrom]" on property "FromLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property. The result of the expression ""Error At :" + @[System:ourceName] + "" + "Error Description : "+ @[System::ErrorDescription] + "" " on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
At last, we tried to span a separate process (System.Diagnostics.Process) for each package. this seems working but taking very long time: A package that normally takes 2 min, is taking 60 min.
We also tried creating an SSIS Package that executes mulitple packages. But only first package is getting executed, and second one is throwing below error (Here the variable it is trying to lock is of first package): "Failed to lock variable "UniqueInstrumentsQuery1" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Please help us with some work around for this. Thanking you in advance,
I m trying to use the web services task, but when i try to run it i got the following error
[Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not execute the Web method. The error is: Object reference not set to an instance of an object.. at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebMethodInvokerProxy.InvokeMethod(DTSWebMethodInfo methodInfo, String serviceName, Object connection) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil.Invoke(DTSWebMethodInfo methodInfo, String serviceName, Object connection, VariableDispenser taskVariableDispenser) at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()".
This is probably because i never worked with this task, even so, does anyone knows what might be wrong?
I got a connection a wsdl, i can configure the service, method and variables(i use fixed values), and i configure the output to a variable with the type object(i have tryed string and int)
I even can download the wsdl file, but the error seems to be in the connection
When running an integration services package from a windows service I get the "Object ... has been disconnected or does not exist at the server." exception after aproximately six minutes of execution.
This is *not* my windows service failing. I can loop indefinately while tracing to a log file within the service and it will run forever. While calling the mypackage.execute(...) method however, after six minutes (give or take) the exception is thrown...
my code looks something like this: <code> dim foo as Microsoft.SqlServer.Dts.Runtime.Application mypackage = foo..LoadPackage(strimportPkgFilename, pkgevents) results = myPackage.Execute(Nothing, Nothing, pkgevents, Nothing, Nothing) </code>
<error> A first chance exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll Exception in: frmMyForm.DoImports Message: Object '/b76f98a0_5bd9_49d8_a524_eeb49d55b303/bqbhkjnaofq_ifr_cwz+srid_1.rem' has been disconnected or does not exist at the server. </error>
oddly, this same code works perfectly if I run it within a windows form application no matter how long it takes.
It also runs fine if the package can complete in under six minutes.
I am new to the world of ETL. I recently started using SSIS and I have come across couple of questions on this.
1.Can we run a SSIS Package using windows task scheduler?
2. If the above answer is yes,I don't have the SSMS installed on my machine. Can a SSIS package run in the absence of SSMS?(I do have Visual studio and SQL server data tools installed)
3. Can a SSIS Package run in the absence of Visual studio and SQL Server Data Tools.Say for example, I create a SSIS package and then remove Visual studio and SQL server data tools from my machine. Will the Task scheduler still be able to run the SSIS package?
4. Does the SSMS free edition comes along with SQL server Agent?
There is not a way to pass parameters to input of Web Service tasks. I heard this problem is fixed with SQL2K5 SP1 and even the online doc says that one can choose either "value" or "variable" when specifying input for web service tasks, but after I installed what-I-think-is SP1, there is still no way to do this.
If one can only specify values (hard-coded) as input to web service tasks, then this would be a very severe limitation. I hope I'm wrong, so could someone please give a pointer. Thanks
I'm pretty stuck on a security issue in SSIS. The web service works by itself, but I can't call it from SSIS, it gives me a 401 unauthorized error. The web service also uses impersonation of my domain admin account.
I have tried the following things: Setting integrated windows authentication in IIS Setting the NTFS permissions of those web site directories to EVERYONE Using a credential / proxy in SSIS and running it from SQL Agent Changing the log on services of MSSQLSERVER, SQLAGENT, and SQL Integration Services to my domain admin account
I can't get anything to work. What is wrong with this thing? Microsoft's security model has gotten completley out of of hand imo
Also in the security event log it shows all authentication as successful.
In SSIS web service task - when you specify the Service and Method in the input tab for a WSDL file being used, it seems to prompt for the parameters to be supplied only in the body of the WSDL file, and not the header.
I need to be able to provide security information (present in the SOAP header of the WSDL) like username, password etc..which is necessary to post any response to the web server, and I cannot see where I can give this in the input tab of the web service task.
Is it possible to do this under SSIS 2005? How? I see I can add a reference to system.web.services.dll.. but then what?
The web service was developed in vb.net/vs.net 2005 and I have no problem adding and consuming it from a web page developed using vs.net 2005 - asp.net/vb.net.
I'm fairly new to the SSAS/SSIS world (though not new on databases, etc.) and I'm having some problems with the SSIS packages in our Cube environment.
Currently in our SSAS/SSIS project, we have two major connection managers, one to the database we use for loading the Cube, and the other connector for the cube itself. To load the data from the database to the cube, we wrote some SSIS packages and used the Analysis Service Processing tasks to process all the dimensions and measures. This works pretty good, so no problems here.
The real problem starts, when I try to change the connection parameters, e.g. because the server changed, or the database has been renamed. As soon as the connection managers points to another (existing) cube, regardless if the structure is exactly the same as the one of the old cube, the tasks lose all the assigned objects from their lists. It is really annoying to add all these exactly same objects to the task again. I tried experimenting with the DelayValidation attribute so the Development Studio doesn't destroy my work every time, but when I deploy the package the Cube breaks. Obviously some kind of deeper connection is destroyed when I change the connection string.
Is there a way to prevent the package from breaking/losing objects, without me having to sacrifice 15 minutes every time I change the connection parameters?
I have written a windows service using VB.NET that connects to a database and performs several tasks. The service is working correctly and has been fully tested against SQL Server 2000. I have recently moved to SQL Server 2005 and now, the service will not connect to the database.
I have setup and tried several different users, but none work, I get the following message:
Login failed for user 'OEESYSTEMS'
The server instance, username and password I specify are all correct.
The service is running on the same server as SQL Server. I can connect through a web application, and through ODBC. The server is set to use SQL server and windows authentication, and is setup and successfully allows remote connections.
It looks like SQL server sees that a windows service is trying to connect and does not allow it.
For the love of god, can anyone help with this issue.
I am running a C# Windows Service as ".Administrator" account. It cannot make connection to SQL Server 2005 and gives the following error:
(An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
Like it couldn't find the SQL Server 2005 .(SQL Server is installed in Windows 2003 Server Enterprise ed.) However when I debug my service as Console application it connects successfully. I am still in trouble.. please help me out.
A common issue that I run across with clients is they want only want to process a file if it's finished transmitting to the server. This SQL Server 2005 task reads the properties of a file and writes the values to a series of variables. For example, you can use this task to determine if the file is in use (still be uploaded or written to) and then conditionally run the Data Flow task to load the file if it's not being used. You can also use it to determine when the file was created in order to determine if it must be archived.
I upgraded the OS on my SQL 2005 SP2 to Windows 2003 SP2. Everything still works fine but now Windows Update says that I need SQL 2005 SP2 again. I checked the version of SQL on the server and it's still SP2. Did the Windows service pack somehow downgrade parts of my SQL installation? I know I can probably just reinstall the SQL service pack but I was just curious...
We deploy an application that uses SQL Server Express 2005. One of our test engineers tried to use our setup on Windows XP SP 3. But the SQL server installer gave an error - unable to update msxml6r.dll. Is this a bug of the SQL Server installer or do we need to make use of a more recent version of msxml6?
Hi! We have next problem in production. We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message).
I am trying to install SQL Server 2005 Standard on my laptop, which has Windows XP Professional with Service Pack 2. I could not able to install properly and i am getting error always. i tried whatever the people mentioned in Google, but I could not able to fix the problem. please some one can help my problem.
Here is the Error I am getting always "The certificate chain was issued by an authority that is not trusted" . I am trying for past few days i could not able to resolve.
please help
Note : I have SQL Server 2000 Client installed in my laptop. this is my development machine
Thanks
Paddy
Here is the Error message from Summary.txt
Product : SQL Server Database Services Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
-------------------------------------------------------------------------------- Machine : W4102762 Product : Microsoft SQL Server 2005 Product Version : 9.00.1399.06 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_w4102762_SQL.log Last Action : InstallFinalize Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted. Error Number : 29515 -------------------------------------------------------------------------------- Machine : W4102762 Product : SQLXML4 Product Version : 9.00.1399.06 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_w4102762_SQLXML4_1.log --------------------------------------------------------------------------------
SQL Server Setup failed. For more information, review the Setup log file in %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt.
Is there a way for a .NET application to receive a notification when a service broker queue has been updated with a new message? I tried using SqlDependency on an SB queue but I got an "invalid" error in my notification handler.
Such a notification would be much better than having to poll the queue every N seconds.
I am attempting to write a Windows service that watches a database for uploaded files to import. When a new file is found, the corresponding SSIS package is run from the file system with variables passed through. I started development as a Windows app and copied the functionality to a service.
The app runs fine. The service does not. I get a "Failure" each time a package is executed. Everything is identical behind the scenes with the obvious exceptions that OnStart and OnStop handlers are buttons in the app. I added a script task at the beginning of one of the SSIS packages to notify me that it is even running at all. It doesn't even hit that initial task.
Again, the app will run all packages just fine. The data is imported and the results return as "Success."
The following is the code executing the package. Any help is appreciated. I've been banging my head on this one for a few days now. (Is there a tag to format a code sample?)
Dim pkgLocation As String Dim pkg As New Package Dim app As New Application Dim pkgResults As DTSExecResult
I am looking at implementing some sort of throttling whereby "expensive" reports are setup as Subscriptions (and hence are run by the Windows Service), while all other requests go the Web Service.
My question is:
Do the two systems share any underlying systems? I understand (of course) that they use the same DB, etc. etc. However, I can't quite tell whether there is an underlying engine that is a common base or if they really just spin up different instances of the same objects and essentially each host the application separately?
SQL Server Reporting Services 2005 Service would not Start on windows server 2003. Working great till yesterday and no changes were made to config file.
Here is the error in Event log
Event Type: Error Event Source: SQL Server Report Service Event Category: None Event ID: 0 Date: 12/13/2007 Time: 8:25:59 AM User: N/A Computer: XXXXXXXXXXX Description: Service cannot be started. Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. at Microsoft.ReportingServices.Library.ServiceAppDomainController.Start(Boolean firstTime) at Microsoft.ReportingServices.NTService.ReportService.OnStart(String[] args) at System.ServiceProcess.ServiceBase.ServiceQueuedMainCallback(Object state) For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Here is i found in log files ReportingServicesService!resourceutilities!4!12/13/2007-08:25:59:: i INFO: Running on 0 physical processors, 1 logical processors ReportingServicesService!servicecontroller!4!12/13/2007-08:25:59:: e ERROR: Exception caught loading and setting code permissions policy level: System.Security.XmlSyntaxException: Invalid syntax on line 158. at System.Security.Util.Parser.GetRequiredSizes(TokenizerStream stream, Int32& index) at System.Security.Util.Parser.ParseContents() at System.Security.Util.Parser..ctor(Tokenizer t) at System.Security.SecurityManager.LoadPolicyLevelFromStringHelper(String str, String path, PolicyLevelType type) at System.Security.SecurityManager.LoadPolicyLevelFromString(String str, PolicyLevelType type) at Microsoft.ReportingServices.Library.ServiceController.SetAppDomainPolicy() ReportingServicesService!servicecontroller!4!12/13/2007-08:25:59:: e ERROR: Error Starting Service: System.Security.XmlSyntaxException: Invalid syntax on line 158. at System.Security.Util.Parser.GetRequiredSizes(TokenizerStream stream, Int32& index) at System.Security.Util.Parser.ParseContents() at System.Security.Util.Parser..ctor(Tokenizer t) at System.Security.SecurityManager.LoadPolicyLevelFromStringHelper(String str, String path, PolicyLevelType type) at System.Security.SecurityManager.LoadPolicyLevelFromString(String str, PolicyLevelType type) at Microsoft.ReportingServices.Library.ServiceController.SetAppDomainPolicy() at Microsoft.ReportingServices.Library.ServiceController.StartService(Boolean firstStart)
I have a problem when i want to debug an ssis component under visual studio 2005.
I'm using sql 2005 with service pack 2 and i have already install patch for visual studio under vista.
When i put a breakpoint for exemple in the method "public override void PreExecute()" or in the other method. "public override void ProcessInput(int inputID, PipelineBuffer buffer)" and i execute the pgm.
however when i begin debugging,it's always skip breakpoint
However when i put a break point in the Validate method "public override DTSValidationStatus Validate()" it function.
In one of my SSIS Project tasks I insert some records into a table. I want to somehow fire off an SSRS report and send this report to some staff members. how would I add a component to reference or run an SSRS report off the table I just inserted to all from SSIS? I assume I'd h ave to create the report in SSRS first, then somehow reference it from SSIS and then figure out a way to run the report, maybe export it into Excel format and send to the users....not sure how to go about all this in my SSIS package.
I have done a full Sql Server install on a server and the SSIS service is not available. Normally when I load Sql Server I get €œSQL Server Integration Services" as a service that I can start. On this machine it is not listed after the install. I don't get any errors through out the install.
Hello! I would like to write a value from a column to a parameter in SSIS with the Execute SQL task. The problem is that I will never get a value for the parameter.
You can recreate the problem with the AdventureWorksDW sample database.
1. Drop an execute SQL task in the control flow
2. Set the connection to the AdventureWorksDw database
3. Write this in the SQL Statement box Select Max(FullDateAlterNateKey) as LastDate
From DimTime 4. Set the resultset to single Row 5. Under result set assign LastDate as the Result Name and create a parameter with a default date. 6. Execute the task, that will finish succesfully but the value of the parameter in 5 have not changed.
I have tried to change the scope to both the package level and the task level without any success. The value of the variable is still the default value. I have also tried a string variable without sucess.
Has anybody developed a ASP.Net app that interfaces to SSIS? If so, what was your experience? Any pitfalls, tips, etc? We have a requirement to launch and monitor SSIS packages via a web interface.
I need to read in general web pages (not a web service) from a typical web site using SSIS and make it available for other SSIS transformations (Script Component). I tried using the XMLSource data source but this appears to require well formed XML, and will not accept HTML which is what I am likely to be getting from the web pages. I tried a HTTP Connection Manager with a DataReader Source, but seems to only accomodate web services.
Can this be done? If someone has an example (tutorial) of how to accomplish this I would greatly appreciate a copy. James
I set up a task to do a transfer of a SQL 2000 db to SQL 2005 in Integration Services (selected my servers, dbs, and chose DatabaseOnline method). In debug mode it processes for a little while and finally errors with:
[2] Progress: Starting database transfer.. Step 1 out of 2 complete
Error: The Execute method on the task returned error code 0x80131500 (An exception occurred while executing a Transact-SQL statement.). The Execute method must succeed, and indicate the result using an "out" parameter.
