Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

SSIS: Object reference not set to an instance of an object..

  
   Database Help (Home) -> Data Warehouse RSS
Next:  CFP: Visual Communication 2008  
Author Message
dmbuso

External


Since: Feb 28, 2008
Posts: 3



(Msg. 1) Posted: Thu Feb 28, 2008 8:04 am
Post subject: SSIS: Object reference not set to an instance of an object Error
Archived from groups: microsoft>public>sqlserver>datawarehouse (more info?)
I'm trying to use a script component to manipulate data in an Access database
to do some custom processing and output to an output row so that I can direct
the output rows to say another Access database.

To accomplish this, I created a script componet in the Data Flow section of
SSIS, checked the Available Input columns that I wanted, created Output
columns of the appropriate data types, and created a script to do the custom
processing.

When I run the package, I receive the error "Object reference not set to an
instance of an object".

Below is the contents of ScriptMain.

' 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
'

Try
'Created by DMB (2/28/0Cool
Static poeID As Integer
Static poeName As String

'Is input row all NULL or all NULL except for field F1?
If (Row.F3_IsNull And Row.F4_IsNull And Row.F5_IsNull And
Row.F6_IsNull And Row.F7_IsNull And Row.F8_IsNull) Then
'Skip current row
Exit Sub
End If

'Create new output record
Me.Output0Buffer.AddRow()

'Set output record fields
If (InStr(1, Row.F1.ToString, "-", vbTextCompare) > 0) Then
'You're on the first row (or a row with only F1 filled in)
Me.Output0Buffer.POEID = CInt(Mid(Row.F1.ToString, 1,
InStr(1, Row.F1.ToString, "-") - 2))
Me.Output0Buffer.POENAME = Mid(Row.F1.ToString, InStr(1,
Row.F1.ToString, "-") + 2)

'Save these in memvars for 2nd row
poeID = CInt(Mid(Row.F1.ToString, 1, InStr(1,
Row.F1.ToString, "-") - 2))
poeName = Mid(Row.F1.ToString, InStr(1, Row.F1.ToString,
"-") + 2)

'Me.Output0Buffer.SECTORCODE = ""
'Me.Output0Buffer.SECTORNAME = ""
Me.Output0Buffer.SEIZUREMONTH = "December"
Me.Output0Buffer.SEIZUREYEAR = "2007"

'The first line contans the # of lines which equal SEIZ
fields, so set these fields
Me.Output0Buffer.COCSEIZ = CDbl(IIf(Row.F3_IsNull, 0, Row.F3))
Me.Output0Buffer.HERSEIZ = CDbl(IIf(Row.F4_IsNull, 0, Row.F4))
Me.Output0Buffer.ECSSEIZ = CDbl(IIf(Row.F5_IsNull, 0, Row.F5))
Me.Output0Buffer.MARSEIZ = CDbl(IIf(Row.F6_IsNull, 0, Row.F6))
Me.Output0Buffer.ICESEIZ = CDbl(IIf(Row.F7_IsNull, 0, Row.F7))
Me.Output0Buffer.METSEIZ = CDbl(IIf(Row.F8_IsNull, 0, Row.FCool)

'Don't set these fields, they're in the 2nd record
'Me.Output0Buffer.COCQTY = 0
'Me.Output0Buffer.HERQTY = 0
'Me.Output0Buffer.ECSQTY = 0
'Me.Output0Buffer.MARQTY = 0
'Me.Output0Buffer.ICEQTY = 0
'Me.Output0Buffer.METQTY = 0

Else
'You're on the second row
'Save these in memvars for 2nd row
Me.Output0Buffer.POEID = poeID
Me.Output0Buffer.POENAME = poeName

'Me.Output0Buffer.SECTORCODE = ""
'Me.Output0Buffer.SECTORNAME = ""
Me.Output0Buffer.SEIZUREMONTH = "December"
Me.Output0Buffer.SEIZUREYEAR = "2007"

'Don't set these fields, they're in the 1st record
'Me.Output0Buffer.COCSEIZ = CDbl(IIf(Row.F3_IsNull, 0,
Row.F3))
'Me.Output0Buffer.HERSEIZ = CDbl(IIf(Row.F4_IsNull, 0,
Row.F4))
'Me.Output0Buffer.ECSSEIZ = CDbl(IIf(Row.F5_IsNull, 0,
Row.F5))
'Me.Output0Buffer.MARSEIZ = CDbl(IIf(Row.F6_IsNull, 0,
Row.F6))
'Me.Output0Buffer.ICESEIZ = CDbl(IIf(Row.F7_IsNull, 0,
Row.F7))
'Me.Output0Buffer.METSEIZ = CDbl(IIf(Row.F8_IsNull, 0,
Row.FCool)

'The second line contans the lbs. data which equal QTY, so
set these fields
Me.Output0Buffer.COCQTY = CDbl(IIf(Row.F3_IsNull, 0, Row.F3))
Me.Output0Buffer.HERQTY = CDbl(IIf(Row.F4_IsNull, 0, Row.F4))
Me.Output0Buffer.ECSQTY = CDbl(IIf(Row.F5_IsNull, 0, Row.F5))
Me.Output0Buffer.MARQTY = CDbl(IIf(Row.F6_IsNull, 0, Row.F6))
Me.Output0Buffer.ICEQTY = CDbl(IIf(Row.F7_IsNull, 0, Row.F7))
Me.Output0Buffer.METQTY = CDbl(IIf(Row.F8_IsNull, 0, Row.FCool)

End If

Catch ex As Exception
MsgBox(ex.Message)
Exit Sub

End Try

End Sub

Public Overrides Sub CreateNewOutputRows()
'
' Add rows by calling AddRow method on member variable called
"<Output Name>Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'

End Sub

End Class


--
Dave B.

 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jan 11, 2008
Posts: 152



(Msg. 2) Posted: Fri Feb 29, 2008 6:44 am
Post subject: RE: SSIS: Object reference not set to an instance of an object Error [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi,
I understand that when you executed your script component, you encountered
the error of "object reference not set to an instance".
If I have misunderstood, please let me know.

This issue seems to be closely related to VB.NET programming. Anyway I
recommend you first debug your script to first identify which line threw
this exception and then let us know the place for further research. You can
refer to the following articles for debugging script:
Debugging Script
http://msdn2.microsoft.com/en-us/library/ms142157.aspx
How to: Set a Breakpoint in Script in a Script Task
http://msdn2.microsoft.com/en-us/library/ms140033.aspx

If you have any other questions or concerns, please feel free to let me
know.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
dmbuso

External


Since: Feb 28, 2008
Posts: 3



(Msg. 3) Posted: Fri Feb 29, 2008 8:25 am
Post subject: RE: SSIS: Object reference not set to an instance of an object Err [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
As I stated in line 1 of my message, I'm using a script component (in the
Data Flow section) not a script task in the Work Flow section, therefore I'm
unable to set breakpoints and determine which line causes the exception.

However, I can try other methods talked about in MSDN article "Coding and
Debugging the Script Component"
(http://msdn2.microsoft.com/en-us/library/ms136033.aspx) to determine the
suspect code.

I guess I was hoping for some help from Microsoft, I'm kind of stuck here.





--
Dave B.


"Charles Wang[MSFT]" wrote:

> Hi,
> I understand that when you executed your script component, you encountered
> the error of "object reference not set to an instance".
> If I have misunderstood, please let me know.
>
> This issue seems to be closely related to VB.NET programming. Anyway I
> recommend you first debug your script to first identify which line threw
> this exception and then let us know the place for further research. You can
> refer to the following articles for debugging script:
> Debugging Script
> http://msdn2.microsoft.com/en-us/library/ms142157.aspx
> How to: Set a Breakpoint in Script in a Script Task
> http://msdn2.microsoft.com/en-us/library/ms140033.aspx
>
> If you have any other questions or concerns, please feel free to let me
> know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>
>
>
>
>
 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jan 11, 2008
Posts: 152



(Msg. 4) Posted: Mon Mar 03, 2008 8:59 am
Post subject: RE: SSIS: Object reference not set to an instance of an object Err [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Dave,
I apologize that I did not pay attention to the fact that you were using a
script component for which debugging with breakpoints is not supported.

To trace the root cause of this issue, I recommend that you use
FireInformation (mentioned in the article "Coding and Debugging the Script
Component") before every place you reference an object. Regarding your code
snippet, I recommend that you use it at the following places:
1. Before your first If statement, check if your passed row 'Row' is null;
2. Check if the column F1 is null before you use it.

You can also have your exception message display more information by using:
MsgBox(ex.ToString())

Hope this helps.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
dmbuso

External


Since: Feb 28, 2008
Posts: 3



(Msg. 5) Posted: Tue Mar 04, 2008 6:40 am
Post subject: RE: SSIS: Object reference not set to an instance of an object Err [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Charles,

Thanks for the tips. I discovered that the F1 field was null. After I put in
checking for Nulls, the script worked great.

Thanks again for the help. Consider this closed.

--
Dave B.


"Charles Wang[MSFT]" wrote:

> Hi Dave,
> I apologize that I did not pay attention to the fact that you were using a
> script component for which debugging with breakpoints is not supported.
>
> To trace the root cause of this issue, I recommend that you use
> FireInformation (mentioned in the article "Coding and Debugging the Script
> Component") before every place you reference an object. Regarding your code
> snippet, I recommend that you use it at the following places:
> 1. Before your first If statement, check if your passed row 'Row' is null;
> 2. Check if the column F1 is null before you use it.
>
> You can also have your exception message display more information by using:
> MsgBox(ex.ToString())
>
> Hope this helps.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================
>
>
>
>
 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
Charles Wang[MSFT]

External


Since: Jan 11, 2008
Posts: 152



(Msg. 6) Posted: Wed Mar 05, 2008 5:34 am
Post subject: RE: SSIS: Object reference not set to an instance of an object Err [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Hi Dave,
Thanks for your response.

I am very glad to hear that this issue has been resolved. If you have any
other questions or concerns, please feel free to let us know. Have a nice
day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 >> Stay informed about: SSIS: Object reference not set to an instance of an object.. 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
SQL Scheduled Jobs - Is there a way to monitor schedule jobs and restart the jobs automatically when it faills and if it fails then notify the responsible person, is there a tool to do this ?

Running total with &quot;treshold value&quot; detection..? - Hi, I have to create a query which evalaute, for each year, the employees who reached a particular number of absences and when this value is reached. I have 1 000 000 records in my absence table. my table is like this: DateID, EmployeeID, ActivityID,...

Problem with multiple instant of SQL Server - I have 2 instant of SQL server on the same machine. 1st instant keep most of the static information - Dimensional data 2nd instant keep most of the transaction information. When I try to use the Analysis Services, I have 2 connection, but they can't ..

Near Real time updating of fact table - -what is the best method that to implement the near real time updating of a fact table without affecting all those users who are reading from it? -fact table in question is only 1.8gb in size, 3.1 million rows, expect near real time updating of approx....

Extraction software - Hi, I need an extraction software that needs to be capable of capturing a table (or tables) displayed on the web and converting it to a file as a list of records with comma separated values. The records will be converted to insert commands after the....
   Database Help (Home) -> Data Warehouse All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]