Editing Input Data in Script Component

ronemac

This is very similar to the post: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1901076&SiteID=1

I am trying to do this. Basically, if the field I am checking contains a space - not an empty string - I want to assign it an empty string so it will get converted to NULL when it hits the SQL destination. The space character from the source is causing the rows to fail I want to force a NULLin the database as a "workaround" from something I am working on.

I thought I had this set up correctly, but it is not working. I have the field in question as an input field set to ReadWrite

If Row.[COLNAME].ToString = " " Then
Row.[COLNAME] = ""
End If

I am getting the following error:

[Assign District Code [12469]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

I can provide more details if needed.

Thanks for your help.

Phil Brammer


Why don't you use a derived column instead Should be a better performing option anyway.

[COLNAME] == " " "" : [COLNAME]

Jamie Thomson

And why are you inserting an empty string rather than NULL

-Jamie

ronemac

I can try that, but it seems to me that I will get the same resulting error Can you be more specific

ronemac

Well, the source is a text file and the values are typed as string. In the SQL table, the field I am trying to get inserted is an int. Not too sure how a null should be assigned to this.

More importantly, I need to first be able to assign a value to the field. Once I have that working with the empty string I can address this.

Regards,

Phil Brammer

ronemac wrote:

I can try that, but it seems to me that I will get the same resulting error Can you be more specific

Try the derived column.

Jamie Thomson

ronemac wrote:

Well, the source is a text file and the values are typed as string. In the SQL table, the field I am trying to get inserted is an int. Not too sure how a null should be assigned to this. More importantly, I need to first be able to assign a value to the field. Once I have that working with the empty string I can address this. Regards,

So let's get this straight. is COLNAME a string or an int because in the code at the top of this thread you are converting it to a string (suggesting its not a string) and straight after you're assigning a string to it (suggesting that it is).

So which is it

Also, if the field you're trying to insert to is an int, why are you trying to put an empty string into the row I'm confused

-Jamie

Jamie Thomson

By the way, Row.COLNAME = Nothign will insert a NULL value (I think).

-Jamie

ronemac

Okay, let me try to clear this up..

All of the fields from the flat file source are of string data type. The SQL data type in the destination for some of them are Int, DateTime, etc.

The first field (out of 4) I am trying to fix is going into an Int SQL field. The creator of this flat file messed up and some of the data comming in has a space character instead of nothing.

The flat file source is a tab delimited text file and instead of having a tab-tab for no value, some of the data for this particular field has tab-space-tab - Do you get it now

See, I am just trying to modify the SSIS package as a work around. Normally, we would just fail the row and let our exception process log it like it had been.

The only thing I want to do is to get these values back to what they should have been - which is an empty string basically.

Jamie Thomson

ronemac wrote:

The only thing I want to do is to get these values back to what they should have been - which is an empty string basically.

Derived Column should do this just fine.

[COLNAME] == " " "" : [COLNAME]

I would still question why you want to change it to a string seeing as the column you are inserting to is an integer so if i were you I would create a new DT_I4 column in the Derived Column component and make its expression:

(DT_I4)([COLNAME] == " " NULL(DT_I4) : (DT_I4)[COLNAME])

-Jamie

ronemac

That's a good suggestions. I will give that a try and then update this post if I am successful.

But, just to be sure you see the whole picture, here is the process flow I am using:

FLAT FILE SOURCE --> SCRIPT COMPONENT (This is where I need to change the data) --> SQL DESTINATION

--> ERROR HANDLER --> continues through error flow

I am assuming the Derived Column Component would go between the FLAT FILE SOURCE and the SCRIPT COMPONENET in my above flow

Thanks

Phil Brammer

Why are you using the script component at all, in the first place The derived column is its replacement unless you haven't told us something.

ronemac

I am using the script component to create a new output column and I am assigning it the value of a parent package variable that is being passed in. I am then using that new output column to update an error log in a "common" database.

The parent package will be called X number of times - each for a different organization - each organization has a unique ID which is being assigned at the parent level and then passed to each of the children. In the event of an error we will know which org the error belongs to so we can inform the org.

Make sense

Phil Brammer

ronemac wrote:

I am using the script component to create a new output column and I am assigning it the value of a parent package variable that is being passed in. I am then using that new output column to update an error log in a "common" database. The parent package will be called X number of times - each for a different organization - each organization has a unique ID which is being assigned at the parent level and then passed to each of the children. In the event of an error we will know which org the error belongs to so we can inform the org. Make sense

Nope. You can create a new column in the Derived Column component with its value being a variable. So I still haven't seen the need for a script component. Just drag the variable down to a new row in the derived column component to create a new column.

jwelch

Jamie Thomson wrote:

By the way, Row.COLNAME = Nothign will insert a NULL value (I think). -Jamie

Or Row.COLNAME_IsNull = True