December 2, 2008 at 4:37 am
Hi all,
i am trying to configure an SSIS package to read a list of servers from a database into a record set,
then for each record in the record set, I want to use the server connection string to dynamically set a WMI Data Reader connection string, to query things such as disk space on the server and return the results to a database table.
Do I need to use a custom script task to set the connection string of the WMI data reader to a variable value? I not quite sure how to read the data set to set the connection string?
Has anyone got any examples they could point me to?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 2, 2008 at 7:14 am
Hi all,
this is actually simplier than I thought, now i've had a little more time to look at it.
I can do it by setting a string variable at the pack level (in theory.....)
I'm still having an issue at present, where I am trying to dynamically set the ServerName of the WMI Connection Manager, by putting an expression on the WMI Connection for ServerName. The expression I'm using is:
"\\\\" + @[User::ServerConnString] - (I've defined @[User::ServerConnString] with a scope of package)
Using breakpoints and watch variables, I can see that the string variable @[User::ServerConnString] is being updated in the for each loop correctly, but when the WMI query executes, it only seems to run against the local machine, ignoring the WMI Connection expression for ServerName ???
I've tried removing the slashes, but this then fails due to 'an invalid parameter'?
Also, if I manually set @[User::ServerConnString], the correct server is queried.
Any ideas, where I am going wrong??
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 3, 2008 at 3:22 am
I found a BUG!
I stumbled across this post, trying to find other examples to see if I'd missed something.
This post verifies the problem (which is dynamically setting the ServerName doesn't work) and the solution (which is to construct the whole connection string).
For full details, check this post:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=377218
Case Closed!
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 26, 2009 at 5:45 am
Hi there,
I was trying the same thing to Cycle trough server names with the WMI Connection to launch queries against multiple servers. I have read the link that you posted but it does not make sense or I am interpreting it wrong (as English is not my first language). Could you if possible could you give us a step by step how to Create the right connection?
I have followed the steps and all I get in the end is RPC server is Unavailable so I take it I have done something really wrong.
Any help would be much Appreciated.
Donovan
January 26, 2009 at 10:54 am
OK,
hopefully I won;t miss anything, but I've attached a PDF (WMI QUERY Guide.pdf ) on how to do a WMI Query for server disk usage.
I think point 8 is probably where you are going wrong.
Please let me know how you get on and if it's any help.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 27, 2009 at 2:20 am
Dave! Your a Legend!!! Works like a charm. Thanks man.
Was quite a lot more complicated than the post makes it out to be, and following your steps to the T was spot on.
Thanks again man
Donovan
January 27, 2009 at 3:06 am
Excellent. I'm Pleased it worked.
I'm just returning the favour that many others have done me on this forumn.:D
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 5, 2009 at 8:26 am
On the Script Component within the last Loop I'm getting:
Name 'OutputBuffer' is not declared.
I went through the pdf and I noticed some parts are missing but I can't figure this part out.
Any ideas?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 5, 2009 at 8:41 am
is it declared as a source?
also. did you define the output columns?
another thing - check that your script containers all the imports - if it doesn't then that could case a problem.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 5, 2009 at 8:48 am
Disclaimer:I am no programmer. This is a weak area for me so terminoligy may be weak.
is it declared as a source?
I have no clue.
also. did you define the output columns?
Yes
another thing - check that your script containers all the imports - if it doesn't then that could case a problem.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
I used the same that you had in the pdf:
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
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called " Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
With OutputBuffer
.AddRow()
.Description = Variables.Description
.DeviceID = Variables.DeviceID
.FreeSpace = Variables.FreeSpace
.Size = Variables.Size
.ServerName = Variables.SystemName
End With
End Sub
End Class
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 5, 2009 at 9:05 am
Is it defined as a source --> I'm referring to when you add the script compenant task in step 12.
I'll see if I can spot anything else. Would a copy of the package be helpful?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 5, 2009 at 9:19 am
Could you check that you have done step 14. - (if I change my package to exclude these variable - I get an output buffer errror
14.Next, select Script and add following variable to ReadOnlyVariables property:
Description,DeviceID,FreeSpace,Size,SystemName
Attached is a copy of the package.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 6, 2009 at 7:27 am
It seems that all of it is correct but I still get the error.
I'm including a pdf attachment that has some of my screens.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
February 9, 2009 at 4:44 am
Found the error!
On Page 2 of your PDF you show the Inputs and Ouputs.
The output name that you have called 'Output 0' is the problem. If you change this to 'Output' and go into your script and come out again (so it recompiles the script) your package should work.
To explain:
in the script, it references 'OutputBuffer', which is the buffer for the output that you specified on the Inputs and Outputs screen. So if you changed the Inputs and Outputs root node for our variables to e.g. DiskUsageVariables.
then within the script, you would have to reference the variables buffer as 'DiskUsageVariablesBuffer' i.e. your script would resemble this:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
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
Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called " Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
With DiskUsageVariablesBuffer
.AddRow()
.Description = Variables.Description
.DeviceID = Variables.DeviceID
.FreeSpace = Variables.FreeSpace
.Size = Variables.Size
.ServerName = Variables.SystemName
End With
End Sub
End Class
Hopefully that makes sense.;)
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
February 9, 2009 at 8:11 am
It makes a little sense.
I made the change to Output like you said and it didn't work. I then changed it to DiskUsageVariablesBuffer like your example and it doesn't work because:
Error1Validation error. Data Flow Task: Script Component [538]: Error 30451: Name 'DiskUsageVariablesBuffer' is not declared. Line 19 Column 14 through 37 Error 30456: 'Description' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 21 Column 28 through 48 Error 30456: 'DeviceID' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 22 Column 25 through 42 Error 30456: 'FreeSpace' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 23 Column 26 through 44 Error 30456: 'Size' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 24 Column 21 through 34 Error 30456: 'SystemName' is not a member of 'ScriptComponent_dac8f37147564a90bcc3dc06c6314fb2.Variables'. Line 25 Column 27 through 46wmi.dtsx00
So, at what level should I declare DiskUsageVariablesBuffer and what data type?
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply