July 3, 2006 at 11:08 am
Hi all,
Let me apologize in advance for what I know is a very basic question, but so far I'm not being terribly successful trying to find sample code to learn from.
I'm trying to write (my first!) .Net Script in SSIS to check for the existance of a file (since that seems to be the only way to do it).
I've created the Script Task, with the following settings:
PrecomplieScriptIntoBinaryCode = False (it complained at one point in time (possibly on validation))
EntryPoint = ScriptMain
ReadOnly Variables = ProductBuyListFileLocation, ProductBuyListFileName
ReadWrite Variable = FileFound
Now the ReadOnly variables have the 2 components necessary for a file location, i.e.:
ProductBuyListFileLocation = 'D:\Testing\ProductInformationEnhancements\'
ProductBuyListFileName = 'buyList.xls'
So, I'm now trying to check the existance with the following .Net Script code:
-------code--------
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
'
' Add your code here
'
Dim FileLocation As String
Dim DTSVariables As Variables
If System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value) + (CStr(DTSVariables("ProductBuyListFileLocation").Value)))) Then
DTSVariables("FileFound").Value = 1
Else
DTSVariables("FileFound").Value = 0
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
-------code--------
if fails with this message, which I'm not finding too helpful:
at ScriptTask_6c9d28f1bd3045739c4237bd588e3835.ScriptMain.Main() in dts://Scripts/ScriptTask_6c9d28f1bd3045739c4237bd588e3835/ScriptMain:line 28
Is it complaining about not being able to find/address my ReadOnly variable? If so, what am I doing wrong?
TIA
July 4, 2006 at 1:24 am
My first thought is that you seem to be concatenating the path twice instead of the path and filename
i.e.
If System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value) + (CStr(DTSVariables("ProductBuyListFileLocation").Value)))) Then
instead of
If System.IO.File.Exists((CStr(DTSVariables("ProductBuyListFileLocation").Value) + (CStr(DTSVariables("ProductBuyListFileName").Value)))) Then
Also, there are a couple of other things I would suggest
a) use IO.Path.Combine to put multiple parts of a path string together. It takes care of the trailing backslashes and other helpful bits
b) Allocate the result of the concatenation to a local variable. This will make it easier to debug
c) Set a breakpoint in your script task and step into the code to see what the full path is
Hope this helps
July 4, 2006 at 4:32 am
thanks for that - silly copy&paste error.
Setting the breakpoint seemed to cause my SSIS to hang on debug - it opens and goes to the breakpoint, but doesn't seem to progress from there, and the 'MS Visual Studio for Applications' doesn't seem to repond - I can't access menu to step through etc.
... and combined with the fact that it seems I managed to do something really embarrassing - I can't seem to find the 'locals' window pane (to see what is happening to the FileFound variable) means I'm struggling to debug 🙁
I can't find anything under 'view' etc. that refers to the locals/watch pane. If I run the package, with breakpoints set, and right-click an ob jects and choose 'add watch' - nothing seems to happen. Any ideas what I've managed to do?
July 4, 2006 at 4:42 am
.. made some progress. I'm debugging, and when stepping through the .NEt script, I get to the end of the script, at End Main, and then .Net launches a 'Find Source: _Startup' window, seeking the location of a file:
ScriptTask_6c9d28f1bd3045739c4237bd588e3835\_Startup
I think that's the route back to the SSIS package? Anyway, I continue debugging, return to Package, but clearly I haven't set the variable, even though the line of code:
Dts.Variables("FileFound").Value = 0
was executed in the stepping through. I set the precedence for the script task to evaluate @FileFound ==0, and set the variable = 1 by default, so if the script correctrly runs, it SHOULD update the value to 0, and allow the precedence constraint to be met, and continue, but it does not - it finishes the script task successfully, and completes package.
Am I being even more blonde here? Doesn't Dts.Variables("FileFound").Value = 0 assign the value 0 to the variable?
July 5, 2006 at 12:50 am
Sounds very odd. I do think that the SSIS Visual Studio interface is a bit flaky and there's been a couple of instances where I've had to scrap packages and start again. Not sure what your particular problem is though.
Other than scrapping your existing script task, recreating it with Try Catch blocks around your code so you can inspect any exception I don't know what to suggest. Sorry.
Daft suggestion I know but you're sure the variable data types are all correct and they all have appropriate scope to be read/written to in the script task ?
July 5, 2006 at 12:55 am
Just had another thought. When referring to variables you need to specify the Variables collection of the Dts object i.e.
Dts.Variables("User::XMLContent").Value
and not
DtsVariables("User::XMLContent").Value
as you seem to have done. Also you'll see in this example I prefix the variable name with it's namespace. It's not mandatory but you never know, it may help in this instance.
July 5, 2006 at 2:14 am
I think I've found the problem. Seems I had 2 variables named the same name, with different scopes - one was package, the other was script. I suspect I was setting the script variable, but checking the package variable. Since deleting the rogue script-scope variable, it seems to be working (in 1 run, at least). I'm making some changes, and I'll be creating a few more scripts tasks that also need tro report on existance, and we'll see how it goes 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply