Scripting component.

Hi Guys,

I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.

I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.

Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.

Any quick help would be really appreciated.

TA

Gemma

Gemma,

Any reason for using the script component to create the file? Can you just use the script to populate the pipeline and use a flat file destination for the file? If so, I recently described how you would go about creating a flat file with header and trailing records.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2168163&SiteID=1

|||

Hi Eric,

I'm just combinging the fields, padding the fields with zero and doing some cleaning of the data as well.

Is there any other way? I've checked your post but I don't know how to add something from the database in the header.

Yes you can enter any expressions but i need the value from the table.

Can you help me do that?

TA
Gemma

|||

Add a boolean flag at the class level and default it to True, Then in the ProcessInput check the flag. If it is True, write the header line and change the flag to false.

There's an example of that approach in the script I posted here - http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx. The example is not what you were asking about, but the script illustrates the technique.

ScriptComponent with multiple ReadOnly variables

I'm having trouble with a script component in which I'm trying to use two ReadOnlyVariables. If I use only one of the two variables, everything works without issue. If I use both of the variables (as part of a comma-delimited list) I get the following:

The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.

I don't believe the variables themselves are the problem. Both are scoped to the package level and I can use either of them if I have it as the only variable. Seems bug-like, but thought I'd get some ideas before pursuing that route.
When you type, "Me.Variables." in your code, what pops up in the list?|||I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.

I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.|||

mr_superlove wrote:

I can't even get to the script if both variables are in the list. I get the message in my first post when I click the Design Script button.

I can move one of them to ReadWrite variables and then typing "Me.Variables" brings up both. If I use it as R/W, however, I get the PostExecute() error at run time.

You are using the comma "," right?|||Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.|||

mr_superlove wrote:

Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

It works great for me. If I type in "myvar1,myvar2" and they do, in fact, exist at the component level (are scoped appropriately), then things work. If I remove the comma, or mistype the var name, I get the error you indicated. I'm sure you've done it already, but double check the spelling and the scope of the variables you are listing in the ReadOnlyVariables section.

Phil|||Are you referencing a variable that has an expression in it that could be causing scope issues, or something?|||

mr_superlove wrote:

Yes. I have Script Tasks where I'm using a comma-delimited list and have no trouble with them.

I took a look at the raw dtsx in a text editor. I noticed a node property for the variable titled "isarray" and it's set to false. I thought perhaps it should be "true". However the package wouldn't load after manually changing it, so I'm back to being stuck.

Just a small thing. There's no space after the comma is there?

If there is, there shouldn't be. People have been caught out by that before.

I know its simple but I just wanted to check.

-Jamie

|||Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.

I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.|||

mr_superlove wrote:

Phil, I want to go ahead and apologize for wasting your time. The moment I saw "myvar1,myvar2" I realized that the extra space I put between my comma and second variable name was the problem and it was.

I'm going to chalk this up to being Monday morning. I appreciate the attention you paid to it.

The interface for this is really bad isn't it? They should provide a combo box for you to select the variables that you want. Or at least explicitly check that the syntax of the property value is correct.

Would you mind raising something at Connect (http://connect.microsoft.com) asking for some changes to this.

-Jamie

Script to change a connection manager

I am looking for code or assistance on how to modify an existing conneciton manger via a script component. I want to have a script change the column sizes for an existing connection manager. How can I get a specific connection manger to change its values.

I have looked at the connection manager, connections, etc classes but I am not certain how to do this as .NET is not my cup of tea.

Thanks.

Once set up though, you can't change column sizes because it will break any associated metadata in the package.|||

Not what I wanted to hear....

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

|||

1Dave wrote:

Not what I wanted to hear....

Well, SSIS can't just dynamically adjust itself depending on any changes to the source. Just not going to happen. Too many components rely on knowing the metadata in advance.

1Dave wrote:

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

Package configurations can change connection strings at run time. You can use an XML file, SQL Server based configurations, etc... You can also override connection strings on the command line, I believe. If you choose to store a connection string in a variable, that variable can be overwritten via the command line as well.|||

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

|||

1Dave wrote:

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

You could treat it as a variable column file and parse it manually inside your data flow with either script or a complex derived column. The connection manager would read the whole row as a single column.

Or, following your original path, you could use code to modify *another* package and execute it. Your original approach won't work because you can't modify the current package. You can modify another package and run that one against your file.

Script Task vs. Script Component

Under "Control Flow Items" appears the Script Task. Under "Data Flow Sources" appears the Script Component. The Script Task also supports .NET scripting so why is it intended to be used with control flow and not data flow?

TIA,

barkingdog

The Script Task executes in isolation. It has no reliance on any other task nor vice versa. Components however work in unison with other components in the same data-flow. That is why the script task works in the control-flow and script component works in the data-flow.

What requirement do you have that caused you to ask?

-Jamie

|||

Jamie,

I don't have any requirements as such. I am learning about SSIS as fast as I can any many of my questions are just about things that seem peculiar to me. The answers I get often help me understand the "bigger" picture of how things in SSIS fit together. In many ways, I'm still at the "how many letters are there in your alphabet?" phase of SSIS

Barkding

sql

Script Question inside a DataFlow

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

gsell wrote:

Is it possible to iterate over all of the fields of the Row collection inside of the Script Component of a data flow. Basically, want I want to is to check every incoming column (all are strings) for a particular character sequence, and if found, change it to something else. I am current accessing each field as Row.Field1, Row.Field2, etc. and just thought there must be a better way to do something like:

For each col in Row

if row.col = XXX then do something.

End For

Thanks in advance for your help

Well, you COULD do that but, trust me, it would be much much slower than what you are already doing. To loop over the data this would need to be an asynchronous (sometimes called a blocking) component and these are slow.

The correct way to do this is what you are already doing.

-Jamie

script component?

My issue: assumed table

id area block basement direction map remarks....more than 90 fields

1 002 Null Y northern Null aaa...

....

I need to put all the fieldname and fieldvalue into another table via id like shown below

id fieldname fieldvalue

1 area 002

1 block Null

1 basement Y

....

and continue,i still have to handle all the data,such as convert fieldvalue's data type and so on

Because my original data get from other component,i cannot use source script component

Any ideas? Regards

seems I need "Public Overrides Sub CreateNewOutputRows()" in transform script component

how can i perform that?

|||

Now,i get CreateNewOutputRows() by creating new output in transform script component

however,how to operate inputed row in that public method?

|||

Code below can accomplish my needs

Public Overrides Sub MyAddressInput_ProcessInput(ByVal Buffer As MyAddressInputBuffer)

While Buffer.NextRow()
MyAddressInput_ProcessInputRow(Buffer)
End While

If Buffer.EndOfRowset Then
MyAddressOutputBuffer.SetEndOfRowset()
MySummaryOutputBuffer.MyRedmondCount = myRedmondAddressCount
MySummaryOutputBuffer.SetEndOfRowset()
End If

End Sub

Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)

With MyAddressOutputBuffer
.AddRow()
.AddressID = Row.AddressID
.City = Row.City
End With

If Row.City.ToUpper = "REDMOND" Then
myRedmondAddressCount += 1
End If

End Sub

From msdn

Script Component: Keyword not supported: ''provider''

Hi,

I have a script component that accesses the database with this codes:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim cn As SqlConnection

Dim SQLCmd As SqlCommand

Dim dr As SqlDataReader

Dim NextKey As Integer = 0

cn = New SqlConnection(Connections.CONNofficetestdb.ConnectionString.ToString)

cn.Open()

SQLCmd = New SqlCommand("select count(*) from accounttrans", cn)

dr = SQLCmd.ExecuteReader

While dr.Read()

NextKey = NextKey + 1

End While

End Sub

But I encounter a problem once I ran it:

Keyword not supported: 'provider'

How do I resolve this?

thanks a lot.

cherriesh

Since you are using the SQL Server connection object, you should not have a Provider defined in your connection string. You only have to supply the Provider in the connection string when you are using OLEDB.

Script component: copy a row into a variable

HI, is it possible to copy a row from the Input0_ProcessInputRow into another variable of a synchronous script component. I have a script component that has three outputs. What I would like to do is the following:

At first iteration, copy entire row into a variable.

For the other rows, is business key cols is different from the previous row (the one copied at previous iteration) or type 2 columns :

1-direct previous row (from the variable) to second.output
2- direct current row to the first output
3- copy the current row into the variable

If the business key cols are the same and type 2 cols are the same:
1-Direct row to the third output

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

The reason I use a script component is that the lookups are not updated dynamically, the rows I need to process may not exist in the target table and once inserted, they are not retreived subsequently by the buffer.

Thank you,
Cco

ccote wrote:

My problem is that when I declare a variable as Input0Buffer and then copy the current row in it, at each subsequent iterations, the variable is updated. What I want to do is to keep the value of the previous row. I know I can do it b are differenty copying the content of all columns into separate variables but since some script components will have 150+ columns, I would like to find a simpler way to do it (like a copy of the structure of the input buffer).

You should not use the buffer that was passed to you outside of the ProcessInputRow call - the data flow engine manages the buffer lifetime, and by trying to use it outside of this call you are breaking the rules.

Try copying the columns to another data structure, e.g. an array.

|||

HI, thank you for your reply. I knew I could copy the row's column to another structure (array, list, variables) but I was wondering If I could copy the whole row into a kind of "row" structure and be able to later direct it to an output. It would have been great since as I said, some of my tables have 150+ columns. I guess, the only way I can manage this is to actually add type1 and type 2 columns to my output and accumulate previous row type1 and type2 attributes into variables. So when the business key changes, I would copy variables into current row and have a conditionnal split that further filter out if any action have to be taken.

I could also use asynchrounous script but there I need to recreate all my 150+ table structure into output buffers. Actually, the real problem is that the lookup transform cache is not updated as new rows are added to the target table. That is why I need to do some custom code like this.

Thank's again,
Ccote

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

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

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

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)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component, dumb question

How do you tell the script task NOT to write a particular row?

This code currently writes 0 and blank when the type <> 4, i'm looking NOT to write the row at all.

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

Private iType, iRest As String
Private rawAmount As Double

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
iType = Row.Type
iRest = Row.rest
If iType = "4" Then
Row.ani = iRest.Substring(112, 10)
rawAmount = CInt(iRest.Substring(41, 4))
If rawAmount <> 0 Then
Row.amount = rawAmount / 100
End If
End If
End Sub

End Class

Also, I'm writting these columns to a destination excel, and event hough the spreadsheet cells are formated for an nn.nn numeric, every cell has an error that my data is text and I get that green wedge asking me to convert it. If I manually enter what I'm sending(example 45.5, it takes it just fine and turns it into 45.50 numeric). What can I do about this? anything additional I can send excel to tell it treat numerics like numerics.. maybe something like: mso-number-format or something.

Thanks for any help or information.

To have a script task not write a particular row, create two outputs, and direct desired rows to one output, undesired rows to the other.

By default, for a given script component, a single output is created. So, create a second output, named "Output 1" by default. Ensure the SynchrousInputID property of the new output is equal to that of the original. Set the ExclusionGroup on both outputs to 1, meaning they are filtered outputs.

In the component, direct rows as needed, connecting the desired output to downstream inputs. Unconnected ouputs are effectvely discarded (technically they are accessible on the disregarded output). Some will naturally reply, you can do similar operations with a conditional split, and that's true enough.

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)

If True Then

Row.DirectRowToOutput0()

Else

Row.DirectRowToOutput1()

End If

End Sub

End Class

|||

Having problems with this. I have a script component in a data flow.

when I add the additional output, the synchronousInput id defaults to 0, and I can't change it to 1133 which is what Output 0 has.

In the code, when I attempt to add code like this

Row.DirectRowToOutput1()Row.DirectRowToOutput1()

property DirectRow* is not available. I must be missing something.

Also, while on this, how does one insert additional rows?

|||

You can't insert new rows into a synchronous output, only into an asynchronous one.

If the script component is set to be a transform (you're prompted for this when you first add the script component), then when you add the new output, the SynchronousInputID should be a dropdown that allows you to select the Input.

|||

re "property DirectRow* is not available. I must be missing something."

I had this problem too - resolved it by setting Exclusion Group from 0 to 1 on both outputs

Script component with multiple outputs

Hi,

I wonder if someone might be able to help me with scripting a script component. I'd like to include error redirection of rows within my script.

If the conversion of any of my inputs fail i'd like to catch the error and then just output all values to another output path. The output path will just take the input values without converting them from string data types and output them to an error table.

In the script i imagine i would use try catch statements and if it fails then set the output. I am not entirely sue as to how to go about switching between outputs though.

Any help on this matter would be greatfully recieved.

Cheers,

Grant

There are some basica exampes of this in Books Online, http://msdn2.microsoft.com/en-us/library/849dd38a-abb5-4702-a413-882aae3980a5(SQL.90).aspx, are they any help?

Yes to the try catch idea.

|||

Here is some additional good links:

http://msdn2.microsoft.com/en-us/library/aa336873.aspx

http://msdn2.microsoft.com/en-us/library/ms135939.aspx

Thanks,

Greg Van

Mullem

Script component to send email

Is there a way I can on any exception, call my .NET code using System.Mail in SSIS 2005 and use my custom code to send emails rather than the Mail component? The mail component sucks, you can't change the look & feel of the email being sent using CSS but if I can fire off my own code to send the email, then that would work great.

To ensure your script task fails on any error, you would create an On Error event handler and add the task in there.

The script task can use just about any .Net component but the system stuff is there already. Try System.Net.Mail for a start and see if that does what you want. If not you may have to get a third-party component, then just add a reference to it. For new components you will need to place them in the framework folder to be able to add a reference e.g. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\

|||So I would just do an OnError then a script component to call my .Net email function? Then just pass through the System variables to the script variable? cool.|||Yes, event handlers are great for this. No more messing about with workflow constraints for this type of package (container) wide handling.

Script Component throws error

Hi,

I have three script component A,B, C. A reads certain data from a table. B inserts records into a .txt file when the recordtype = 1. C inserts records into same .txt file when the recordtype = 0. Now I receive a script comp error for C as follows.

[Error Desc]

************************************************************

The process cannot access the file 'D:\Documents and Settings\Administrator\Desktop\AuditLog.txt' because it is being used by another process.

************************************************************

at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

at System.IO.StreamWriter.CreateFile(String path, Boolean append)

at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

at System.IO.StreamWriter..ctor(String path, Boolean append)

at ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54.ScriptMain.PreExecute() in dts://Scripts/ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54/ScriptMain:line 19

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

[/Error Desc]

Can anyone throw some light on it.

Thanks

If you have two script conponents trying to write to the same file at the same time, then this would seem a reasonable error. You will need to serialise the two write operations, as only one process can have a file open at a given time.|||

Hi,

Thanks for your reply. After completion of first script component only next gets executed.

In my example after B completes C starts. Also I have closed and disposed the stream writer object in the post execute of the script component B

How I can get rid of this. Also suggest any alternative solutions?

|||

Are all the script components in the same data-flow? If so then there is nothing to stop them trying to access the same file at the same time given that each component operates on one data buffer at a time. Each data buffer is a subset of all the data in the pipeline.

If you want to ensure that the script components are not accessing the same file concurrently put them in seperate, sequential, data flows. Or put them in 3 seperate files and then put them all together into a single file using UNION ALL component in a seperate data-flow.

-Jamie

Script Component Task: function that returns last word in a string

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools' and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

I believe you'd want something along the lines of:

\s*(\w+(\s+(Corp|Ltd))?)$

Public Function CleanString(ByVal CustomerName As String) As String

Dim re As New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

m = re.Match(CustomerName)

' insert sanity to make sure we had a match

' group 0 would be the full string

' group 1 is the one we want

Dim g As Group = m.Groups(1)

Dim cc As CaptureCollection = g.Captures

CleanString = cc(0).Value

End Function

|||

Matt,

Thanks the code almost worked--I had to rewrite it like this. However I need help to tweak the regular expression Regex("\s*(\w+(\s+(Corp|Ltd))?)$") Im getting the following results In/Out

Input: Digidesign Australasia Pty Ltd Output: Pty Ltd --wrong should be Ltd

Input: Ms S Aiten Output: Aiten --correct

Function CleanString(ByVal CustomerName As String) As String

m = re.Match(CustomerName)

' group 0 would be the full string

' group 1 is the one we want

CleanString = m.Groups(1).Value

End Function

'Called by

Public Class ScriptMain

Inherits UserComponent

Dim re As Regex = New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Len(Row.CUSTOMERNAME) > 0 Then

Output0Buffer.oCUSTOMERNAME = Row.CUSTOMERNAME.ToString

Output0Buffer.oSearchName = CleanString(Row.CUSTOMERNAME.ToString)

End If

|||I don't think you can easily do this with a single regex. You might want to pre-process the string to remove the Pty first, or have a special case that captures the words you want to the left and right of "Pty" whenever the string appears in your customer name.

script component not executed

Hi

I have a ssis project that contains a parent package and 2 child packages. The parent package loads data from multiple flat files into a database and then kicks off the 2 child packages using separate execute package tasks.

The child package has a data flow.Within the data flow data is extracted from a database. The data is transformed using a script component and then loaded into a second database.

The problem I have is that the second child package is not working. It appears as if the data is being extracted fine.However the script component does not seem to be being executed so the columns that are being transformed are not being changed and so the write to the database fails. When I send the error rows to a database table with all the fields varchar(200) the write completes but the transformed columns are blank.

Also if I put a message box or ComponentMetaData.FireInformation in the script component I get no output.

However when i run this project on my development machine it runs fine but when I run it on the staging server it gives the problems explained above.

Any ideas please?

Thanks

G

You have two child packages. Do they both have scripts? Do the child packages run in-process or out-of-process?

How are you executing the packages in each environment? Any differences there?

script component Name Ouput0Buffer is not Declared?

Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

End Sub

There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.

Do you need to add a new row? Or are you just assigning a value to a new column on the existing row? The Row object should expose all of the columns you added.|||

JCNET wrote:

Doing a simple test with a script component in a DataFlow to transform some data from a flat file. I have new columns under the default Ouput 0 .. however in my code when I try this, I get the above error.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Output0Buffer.AddRow()

End Sub

There will of course be a lot more code in the Script Component, but not clear on why I can't reference it.

Is this a synchronous or an asynchronous component? If it is synchronous then calling the AddRow() method is not valid. You cannot add rows to the output of a synchronous component.

-Jamie

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

script component isn't finding a reference dll

I have created this c# dll for one of my packages and I was planning on calling it from the script component, but for some reason when I try to call it I get the following error.

Could not load file or assembly 'VRS.Utilities.Dates, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

I've dropped the dll file in the WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and it shows up when I go to add the reference however when I try to implement it I get the error.

Any idea's on how to fix this?

Thanks for the help
Saitham8

Add the DLL to GAC.

At run time the script task will check the DLL in GAC, at design time script task will check the DLL in folder WINDOWS\Microsoft.NET\Framework\v2.0.50727

|||That seemed to be the solution thanks for the help

Script Component in loop tries to evaluate connection

I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.

The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.

I hope this makes sense...

Set the DelayValidation property to True for the connection (and data flow task if required).

Script Component failed validation and returned validation status "VS_ISBROKEN". Error

I have been running this package before (with no errors) but now I get this error message. Is it my enviroment that is broken? All my Script Compnents end up with this error code.

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Script Component" (53)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "src_abs_budg_sales_wc.dtsx" finished: Failure.

The CLSID quoted in the error message is the Visual Studio for Applications runtime, progid "VsaVbRT.8.0," described as "Microsoft Visual Basic Scripting Engine." It looks like the corresponding COM DLL is VsaVb7rt.dll. So it looks like your VSA installation is corrupted or incomplete. Can you try re-registering that DLL using regsvr32.exe?|||

It was my installation that was corrupted, thanks