Wednesday, March 21, 2012

Does not Work --ETL Package Tutorial (Lesson 1: Creating the Project and Basic Package )

Hi,

I am new to SSIS. I followed the direction of the tutorial Creating Simple ETL Tutorial package in BooksOnline. I have tried more than five times and have done exactly as suggested in the tutorial but it does not work.

The URL is:

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

I get these warnings and finally fails:

1)[Lookup [30]] Error: Row yielded no match during lookup.

2) [Lookup [30]] Error: The "component "Lookup" (30)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (32)" specifies failure on error. An error occurred on the specified object of the specified component.

3) [DTS.Pipeline] Error: The ProcessInput method on component "Lookup" (30) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

4) [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

Can someone help me with this tutorial error? or Am I doing something wrong.

Thank you,

a lookup transform will trhow an error if there is no a match; check the 'joins' within the lookup and additionaly configure the error output to either 'ignore' or 'redirect' the errors. Redirect option will require to define a path for those no matched rows.

Rafael Salas

|||

Hi

I have the same problem as above ( I have done the tutorioal on two different machines) . I have looked at the sample data and the lookup value is there. The only thing that I have noticed is that the dates in the flat file appear to be US format. Is this the problem?

|||

As I suspected this is a date format problem. I have added a script component to modify the date and all is well. Script text is as below.

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)

Dim YY, MM, DD As Integer

Dim StrYY, StrMM, StrDD As String

YY = Year(Row.CurrencyDate)

MM = Month(Row.CurrencyDate)

DD = Day(Row.CurrencyDate)

StrYY = Trim$(Str$(YY))

StrDD = Trim$(Str$(MM))

Select Case DD

Case 1

StrMM = "January"

Case 2

StrMM = "February"

Case 3

StrMM = "March"

Case 4

StrMM = "April"

Case 5

StrMM = "May"

Case 6

StrMM = "June"

Case 7

StrMM = "July"

Case 8

StrMM = "August"

Case 9

StrMM = "September"

Case 10

StrMM = "October"

Case 11

StrMM = "November"

Case 12

StrMM = "December"

End Select

Row.CurrencyDate = DateValue(StrMM & " " & StrDD & ", " & StrYY)

End Sub

End Class

|||

You can solve this problem configuring the LocaleID in "Sample Flat File Source Data" to "English (United States)" because this is the LocaleID of the Data Base.

The problem is that LocaleID in your Regional Settings is not 1033 (English (US))"

No comments:

Post a Comment