I spent 2 days creating a package to move data from a Dynamics NAV SQL database to our ActionPak database and got it working.
2 weeks later I decided to break the single package into multiple smaller packages since the execution frequency was not the same on all of them only to find that one of the objects would crash Visual Studio if I tried to delete it.
I had to copy and paste all the variables and other objects out into new packages, then re-assign the data sources on all of them. Then I had to re-write the object that was crashing visual studio by hand. That cost 8 hours.
The offending object had a data viewer on one of the connectors that was causing the crash so from now on I am not leaving any viewers in my projects, I will delete them before saving and exiting, and I am going to make my packages as small as possible so I don't loose 100% of my work if one glitch like that happens again.
SSIS is also very slow to refresh the screen, and the properties are very painful to edit. I hope the next release is a lot smoother.
Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts
Tuesday, October 21, 2008
SSIS Scripts
I have been working with SSIS for a couple weeks, and while I understand a few of the common tools I am really a novice in many areas.
Today I needed to prevent the Available Quantity on inventory from going negative since we don't want to expose that to users of the site I am working on. There are no build in commands to do that in the "Derived Column" object, so I had to use a script object.
For some reason VB is the only language supported, but it was really easy. After identifying which columns were available and whether they were read-only or not, the only code I had to write was:Row.AvailableQty = Max(Row.AvailableQty, 0).
The entire script follows, all but one line was generated by the system.
' 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 Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Row.AvailableQty = Max(Row.AvailableQty, 0)
End Sub
End Class
Today I needed to prevent the Available Quantity on inventory from going negative since we don't want to expose that to users of the site I am working on. There are no build in commands to do that in the "Derived Column" object, so I had to use a script object.
For some reason VB is the only language supported, but it was really easy. After identifying which columns were available and whether they were read-only or not, the only code I had to write was:Row.AvailableQty = Max(Row.AvailableQty, 0).
The entire script follows, all but one line was generated by the system.
' 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 Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Row.AvailableQty = Max(Row.AvailableQty, 0)
End Sub
End Class
Subscribe to:
Posts (Atom)
