developer.* Blogs - SSIS (SQL Server Integration Services) - Comments

Thank you!

Guest — Mon, 30 Apr 2007 16:44:12 -0700

I was getting slammed by nullability until I came across your post. Thank you for posting this! Rick

I've just been asked to

Guest — Wed, 25 Apr 2007 12:04:52 -0700

I've just been asked to create a SSIS solution for our company. This is really a great jumping off point to get the information I need to start...

Thanks
-WM
DBA

Not Sure...

Daniel Read — Wed, 18 Apr 2007 09:08:57 -0700

Sorry, dave, but nothing jumps out at me other than the use of "RdVar" vs. "ReadVariable"--but that may have just been shorthand you're using for your example.

One possibility does come to mind: is it possible that you've also declared the "SQL" variable as read only in the Script Task dialog box? That might cause the behavior you describe. When using ReadVariable() and WriteVariable() you do not need to (and probably should not) declare the variables on the Script Task "Edit" dialog as available to the script.

Hope that helps,
Dan

Cannot write to package level variable

dave — Tue, 17 Apr 2007 16:59:21 -0700

Hi Dan,
I still cannot write to a package level variable in a Script Task. I have 2 variables to create a SQL statement.

  Dim LastRecord As String, sSQL As String
  LastRecord = CType(ReadVariable("LastRow"), String)
  sSQL = "SELECT * FROM MyTable WHERE RecID>" & LastRecord
  WriteVariable("SQL", sSQL)
  sSQL = CType(RdVar("SQL"), String)
  MsgBox(sSQL)

I'm able to read the value of LastRecord but when I appended to sSQL and call WriteVariable, the MsgBox () shows only the initial value of the Package variable (SQL). Apparently it did not save it... any help???
Thanks in advance

Fast Load is it!

Daniel Read — Tue, 17 Apr 2007 08:20:05 -0700

Well all right! Thanks to Nick and Guest for encouraging me to look closer at the Fast Load option of the OLE DB Destination. For some reason I was sure that Fast Load was no good unless everything was local, but I think I may have been confusing that memory with a situation where I was using a Flat File Source...

Anyway, in my current project when I changed the OLE DB Destination to use the Fast Load option, sure enough the "Keep identity" option was there, and when I tested it using a remote database connected via SQL authentication, it worked great. I'll update the top of my post so that new readers won't have to wade through my rant to find the good stuff.

Thanks again for the suggestions.

Dan

Slight variation when in Script Components

MikeG — Tue, 17 Apr 2007 07:36:24 -0700

Hi Daniel:
As the guest poster on 3/31/07 reported, I also found I had to modify the syntax slightly when using these functions in a data flow Script Component (these functions work perfectly in control flow Script Tasks). Don't know why there's a difference, but the "vars" object variable has to be declared as:
Dim vars As IDTSVariables90
And the VariableDispenser appears to be a static class owned by the Script Component. The "Dts" namespace causes an error, so the following works fine:
VariableDispenser.LockForRead(varName)

BTW: One big reason for using these functions is because SSIS seems to mangle variable names passed to Script Components. When using the ReadOnlyVariables property of the Script Component, my variables (ImportFile_Name and ImportFile_Path) are missing their underscores within the Script Component's code (ImportFileName and ImportFilePath). This made it pretty hard to use the "Me.Variables." as documented in online help.

IMO, it's much cleaner and symmetric to use your functions for reading and writing variables, rather than relying on the ReadOnlyVariables and ReadWriteVariables properties. Code is much easier to read and understand when using your functions.

OLE DB Destination != SQL Server Destination

Guest — Tue, 17 Apr 2007 07:07:03 -0700

I believe you are confusing OLE DB Destination with SQL Server Destination. An OLE DB Destination, even with fast load, does not require local resources.

Re: Fast Load = Bulk Insert

NickFoster — Tue, 17 Apr 2007 01:29:37 -0700

Dan,

Sorry this wasn't of use to you. If it helps clarify anything the databases I was transferring between were on separate servers (and I was running the integration services package from my desktop) but I was using SQL Authentication which is my preferred access method as we tend to work across different domains.

Cheers,
Nick

Fast Load = Bulk Insert

Daniel Read — Mon, 16 Apr 2007 16:23:54 -0700

Hi Nick,

Thank you for mentioning this, Nick. I had forgotten that possibility. However, if I'm not mistaken, there's a good reason I didn't think of it as an option: choosing "Fast Load" is the equivalent of doing bulk insert, which brings with it the requirement for the data source, SSIS instance, and data target to be on the same machine as local resources and logged on with Windows authentication. This just isn't viable in my situation.

Thanks again,
Dan

Fast Load

NickFoster — Mon, 16 Apr 2007 03:48:14 -0700

I had exactly this problem today. However I found that if you change the data access mode of the OLE DB Destination to "Table or View - fast load" it gives you the options "Keep Identity" and "Keep Nulls".

Hope this helps,
Nick

Much appreceiated

Arthur Hill — Mon, 02 Apr 2007 09:53:28 -0700

I have struggled for a week with the 'Can't get lock on variable' error meesage. I used your example and it all works fine. Please send this to MS and tell them to include it in their documentation.

Thanks again.

Variable naming in your procedure

Arthur Hill — Mon, 02 Apr 2007 09:14:30 -0700

Everything I read says you have to refer to variables by their scope, such as 'User::DirectoryIncoming' or 'System::Something'. In your example above you do not use the namespace qualifier. Why? Is it not necessary?

ssis vars in scripts

Guest — Sat, 31 Mar 2007 13:28:22 -0700

This stuff is a life-saver. I could use your
Dts.VariableDispenser... for a scripts task, but could not for a script component which was reading a table. In your code above (both for read & write) I had to change the "DTs" to "Me", then variable collection was changed also. Here's the variantions on routines:

   Private Function ReadVariable(ByVal varName As String) As Object
        Dim result As Object
        Try
            Dim vars As IDTSVariables90
            Me.VariableDispenser.LockForRead(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
        Return result
    End Function

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
        Try
            Dim vars As IDTSVariables90
            Me.VariableDispenser.LockForWrite(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

This is probably not the way to go, but maybe you can improve it. Thanks much

SSIS Scripting Shortcomings

Daniel Read — Sun, 18 Mar 2007 15:33:00 -0700

imFrustrated wrote: "Is there no global scripting library that all script task have visibility to?"

Not as far as I can tell, there is not, no. The scripting integration at both design time and runtime is pretty clunky. You will do a lot of copying and pasting. When I was writing a lot of code in SSIS I had a I hope this is one area they will be able to improve in future releases.

Dan

P.S.
See also this post for another example of what I mean by "clunky."

Just so I understand

imFurstrated — Sat, 17 Mar 2007 07:28:21 -0700

Nice blog post first of all.

I am new to SSIS, is what you are saying that I have to copy these functions to each script task? Is there no global scripting library that all script task have visibility to?