May 21, 2008 at 10:00 am
Maybe I am just missing something (like the entire .NET revolution...)
Being absolutely wet behind the ears with .NET, I am really struggling to come to terms with the scripting tasks in SSIS.
I have a variable (in this case called varRunBalances - Boolean) for which I set the scope to the entire package.
Now during one of my dataflows I calculate if the Run Balances or not using a column derivation.
For me the logical next step would be to create a script destination transformation where I have a simple statement
If the run balances, then set the variable equal to True, else set the variable equal to False.
How would I do this in a scripting component?
:blush:
May 21, 2008 at 10:43 am
Where are you putting if the "Run Balances"? You are calculating this in a derived column? A derived column executes once per row in the data flow and adds or modifies columns in that data flow, so I must be missing something.
You can set a variable value in a script component within a data flow. You have to set the read/write variables on the component to include the variable you want to update. I don't think you should have any need to to this though. Perhaps you could explain a bit better what you are trying to do. There are some count and aggregate components that will keep running totals of things and set variables for you without any code.
May 22, 2008 at 12:31 am
I am using the varRunBalances as a boolean variable which will indicate that my control flow is still in balance .
For example:
I have a source file, and a checksum file from a source provider who is notorious for not believing in data integrity (actually it took a lifetime of insisting before they gave me this prized checksum file)
The first task that I do is to perform a rowcount transformation against the sourcefile (due to change this to an aggregator, because I need to sum on some fields later, but rowcount is fine for this example).
I load this to a variable, and then merge this with the rowcount found in the checksum file, and used a derived column to work out if the run still balances at that point.
What I want to do is end this with a scripting destination where I set the run balances variable.
However, I want to re-use this run balances variable at several other intervals simply to make sure that I am loading the correct data once I get to the very final step.
Then, wherever I set the run balances, in the control flow I can branch out with expressions to email and force my run into failure.
Therefore, my variable has a scope of the package rather than the data transformation.
The forcing to failure is because all this will sit in a for each file enumerator, and I dont want to process any further once the run doesnt balance.
I have everything else working perfectly, just not the setting of the variable.
I added varRunBalances as a readwrite variable to the script, and tried the following:
Dts.Variables("varRunBalances").Value = False
Doesnt like this (I think its because its a scripting destination?)
Then I tried the following:
Me.VariableDispenser.LockForWrite("varRunBalances")
Me.ReadWriteVariables("varRunBalances").Value = False
This compiled fine, but when it runs, it comes back with an error saying that the variable is locked.
What am I missing here (the obvious thing is some serious experience in .NET coding)
May 22, 2008 at 2:17 am
Note bing the kind of person to hang around and wait, and after a frustrating morning on google, I managed to locate some really interesting examples.
But the one that did it for me came from Conchango (When in SSIS doubt, ALWAYS look at Conchango first, Jamie you rock!)
I know I should really have put this into a PostExecute method, but I felt there wouldnt be any real difference if I were to do it in the ProcessInputRow method.
Herewith, my code used
'1. Local variable declaration
Dim RunBalances As Boolean
'2. Decide if the run balances(true) or not (false)
If Row.FileBalances = 1 Then
RunBalances = True
Else
RunBalances = False
End If
'3. Add the package variable to the variable dispenser
Dim vars As IDTSVariables90
Me.VariableDispenser.LockOneForWrite("varRunBalances", vars)
'4. Set the package variable, and unlock it
vars("varRunBalances").Value = RunBalances
vars.Unlock()
Once more, kudos to the fellahs at Conchango
June 24, 2008 at 9:07 am
Hi all,
I am very new to SSIS and microsoft technology please help me!!
I followed same steps to update my variable but no success.
I declared a variable as varPUI & set default value as "OLD_VALUE" and wrote the below code in script component
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
With Row
Me.VariableDispenser.LockOneForWrite("varPUI", vars)
vars("varPUI").Value = "New Value"
vars.Unlock()
End With
End Sub
the updated varaible I am writing into a flatfile
it is not updating the variable. actual code I want to write is something else if it works fine.
Please help me where I am going wrong
Thanks in advance
Regards,
-Srinath
June 24, 2008 at 9:13 am
hey Srinath,
I can recall when I updated my first variable in a scripting destination, and it was quite interesting....
First off, are you updating your variable, in your controlflow, as a scripting source, transformation, or destination.
There are methods for each of these.
If its a scripting destination, or even transformation, then first off, why?
Microsoft deem it to be bad practice. In saying that, there is a time and a place for everything. Justify, and we will see what we can do
~PD
June 24, 2008 at 9:49 am
Hi PD,
Thanks for a quick response, I was getting mad with this.
Iam doing it in data flow on transformation and code is written in scirpt component.
my actual requirement is I want to generate sequence number for a duplicate ids like.
ids rowCount(which I need to generate)
C_1000075 1
C_1000147 1
C_1000147 2
C_1000271 1
C_1000271 2
C_1000271 3
Please help me how to do it in SSIS. I am a informatica person, there i can do it very easily.
requirement is very simple but I am unable to implement with SSIS
Thanks in advance
Regards,
-Srinath
June 24, 2008 at 10:12 am
Look at the following posting:
http://www.sqljunkies.com/WebLog/sqlbi/archive/2005/05/30/15684.aspx
This will give you a fairly good idea of what needs to happen, in order for you to set a value during your transformation, and use this as a new column.
Please shout if it doesnt make sense.
~PD
June 24, 2008 at 10:28 am
No it is not updating the variable
I really dont know where exactly I am going wrong
what I am doing is,
declared a variable called varPUI at package level as int32
reading the excel file--> sorter trans...-->script component(to generate seq. numbers)---> derived column trans(to derive new column from variable)---> flatfile destination
in script component I have put varPUI variable under read write variables.
and below is my code in script component (I tried many options)
----
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
Dim vars As IDTSVariables90
Dim i As Int32
Public Overrides Sub PreExecute()
MyBase.PreExecute()
i = Variables.varPUI
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
' Me.Variables.varPUI = "1"
' MsgBox("inside")
i = i + 1
End Sub
' Public Overrides Sub PostExecute()
'Dim vars As IDTSVariables90
' Me.VariableDispenser.LockOneForWrite("varPUI", vars)
' vars(0).Value = i
' vars.Unlock()
'MyBase.PostExecute()
' Me.VariableDispenser.LockOneForWrite("varPUI", vars)
' vars("varPUI").Value = ss
' vars.Unlock()
' End Sub
End Class
----
June 24, 2008 at 10:56 am
Whoa!
Quite a messy script....
Without looking at that script, here is one (assuming that you use varPUI, and that varPUI is in the read/write section of your variables)
- Add a pre-execute SQL task, which will get the max sequence number from your table
Add a variable called varmaxPUI
Add a TSQL statement "SELECT COALESCE( MAX(PUI), 0 ) AS LastPUI FROM yourTableWithSequenceNumber"
Change the Results Set to Single Row
In the Results Set section, add a Results set. Result Name = LastPUI, Variable Name = User::varmaxPUI
Why? Because this will give you the last sequential number from your table (assuming that you want to insert using the next sequential number
- Add a transformation script, and add varPIO to the read/write
In the Inputs and Outputs Section, Under Outputs, add an output, and add a column. NB: Make sure the output remains Output 0.
Name the column PUI ---> Four Byte Unsigned Int works for me
- In the script design, add the following:
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
Dim NEXTPUI As Int32
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
NEXTPUI = NEXTPUI + 1
Row.PUI = NEXTPUI
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
NEXTPUI = Variables.varmaxPUI
End Sub
End Class
Notice a couple of things:
a) NEXTPUI is declared inside the class, not inside any of the Subs
b) The PreExecute simply sets the NEXTPUI equal to what the variable returns
c) You increment NEXTPUI on a per row basis, and provide that detail to the PUI column
Try it, and let me know
~PD
June 24, 2008 at 11:09 am
HI PD,
MANY MANY THANKS FOR YOUR KIND HELP!!!
it worked fine
shame on my ignorance
your help made my day
Regards,
-Srinath
June 24, 2008 at 11:14 am
Only a pleasure Srinath,
I know what its like when you are just unleashed on SSIS, you tend to scan the fine print and go for what you are after.
Many a time I have revisited an article quite a few times because I did this, normally when there is a deadline.
Therefore, I am busy (have not yet perfected) disciplining myseelf to read from start to finish thoroughly.
Good luck on your endeavours with SSIS man
~PD
ps> Read up on package configurations early, you are going to need them soon enough
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply