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))"

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))"

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))"

Does it require sa to create dts package?

what privilege does the user has to have to create a dts package?By default, anyone with a login can create a Package as the
public role has execute permissions on sp_add_dtspackage
stored procedure in msdb and the guest account is enabled in
msdb by default so it depends on whether you have changed
this or not.
But they don't need to be a sysadmin to create packages.
It's generally controlled through sp_add_dtspackage.
-Sue
On Thu, 24 Feb 2005 09:35:07 -0800, "sabby"
<sabby@.discussions.microsoft.com> wrote:

>what privilege does the user has to have to create a dts package?|||Thank you so much.
"sabby" wrote:

> what privilege does the user has to have to create a dts package?

Does Debug > Build in Script Task actually do anything?

I'm looking for some way to verify the syntax, references, variables, etc. in my Script Tasks without having to run the package or the task.

There's a Build command in the Debug menu, but it doesn't seem to do anything -- certainly, not like the "Build Solution" in a Visual Studio project.

Am I missing something? Does Debug > Build in the VSA editor actually do anything?

Thanks!

- Mike

mike.groh wrote:

I'm looking for some way to verify the syntax, references, variables, etc. in my Script Tasks without having to run the package or the task.

There's a Build command in the Debug menu, but it doesn't seem to do anything -- certainly, not like the "Build Solution" in a Visual Studio project.

Am I missing something? Does Debug > Build in the VSA editor actually do anything?

Thanks!

- Mike

Mike,

I don't think it does for SSIS, no. This is a legacy from using someone else's IDE in order to facilitate scripting.

-Jamie

Does a checkpoint file record a package's state

The state of a package at any one point in time is determined by the values in all of its user variables as these are the only properties in the package that can be changed and persisted throughout the runtime of the package.

Is the package's state stored in a checkpoint file in the event that the package fails? In other words are the user variable values stored in the checkpoint file?

Thanks

Jamie

Can task A update an enivornment variable i.e. persist the state outside the package.

Out of interest viw does package B need to update a variable in package A.

|||

SimonSa wrote:

Can task A update an enivornment variable i.e. persist the state outside the package.

Not in this scenario!

SimonSa wrote:

Out of interest viw does package B need to update a variable in package A.

[I presume that word was "why" :)]

That's a question that needs to be answered at length - preferably over a few beers!

-Jamie

|||

Jamie,

Can you ellaborate on what this variable (PkgA) represents (I.E., some sort of count, threshold, clock/time, etc...)?

We've had scenerios where a variable needed to be updated and couldn't be done using a GLOBAL_ENV_VAR or some other global, it needed to be updated per client(job), and client(job) specific function. So what we did is created and modified text-based files to update the variables values.

Say for instance, you had to update variableA (which was a boolean true/false), you could create/modify a file specifc to that instance/job run, and change the "variableA=" to true of false. Then you could always refer back to that specific file to lookup the values - like creating your own checkpoint files.

|||

Jason,

The particular one one in question was a variable called PackageStack which maintains a comma seperated list of packages that have been called, in the order that they have been called.

Each package has an OnPreExecute event that "pushes" the name of the package into the PackageStack variable and an OnPostExecute event that "pops" it off.

-Jamie

|||

Similarly, you could log this using a log provider, however, you will run into the same problem I am facing - with that logging bug and only logging the immediately inherited pkg!!!

Maybe, whom ever answers this post can solve both our problems...

|||

Yes, the values of the variables are stored in the checkpoint file.

K

|||

I disagree, I believe this is only true for simple variables. I have stored a recordset in a variable and this wasn't stored in the checkpoint file.

Either object types aren't supported or there is something about what is saved.

What happens if a variable is changed in a loop. What is stored in the checkpoint, the variable value at the start of the loop or the value at the point of failure.

|||

Correct, object types are not stored.

ForEach loops start at the beginning of the collection.

For loops continue where they left off.

They all key off of the variables.

K

|||

KirkHaselden wrote:

Correct, object types are not stored.

Why not?

Will this change?

-Jamie

|||

I've just come across a differrent "problem" with this.

I have 2 packages pkgA and pkgB. pkgA calls pkgB

pkgB has 2 tasks taskA and taskB. taskA is a script task that updates a variable (varA) in pkgA. taskB is a data-flow that is failing.

pkgB uses checkpoints

Now, I can correct the error that causes taskB to fail but the problem I have is that when I rerun it using the checkpoint file taskA will not execute and therefore varA will have the wrong value in it.

There isn't a way around this problem currently but is there a way that this could be catered for in the future using checkpoint files? i.e. The checkpoint file for pkgB would have to store the value of varA even though varA is in pkgA.

-Jamie

Documenting SQL 2005 packages

Hi all

I would like to select transforms in a package and paste them as jpeg or similar into word or powerpoint in order to allow me to create a separate set of documentation.

However, whenever I copy some transformations and jump to word, the copy buffer is empty.

Any suggestions?

Thanks

I am not sure you can do what you are after, but I am intrigued. What do you mean by copy a transform? What do you expect to see?

|||

I would like to be able to paste the 'graphical' representation of a set of SSIS objects into a word or ppt document as a jpeg or similar.

|||

Does print screen not work for you?

-Jamie

|||

Hi Jamie

The problem with print screen are:

* Firstly, the quality of the image is quite poor by the time it is dumped into word,

* But, more importantly, if all the objects don't fit on the screen at one time, then print screen wont capture them.

I could easily solve the 2nd problem by zooming out, but then I lose all detail and defeat the purpose.

A number of my data flows have 20+ objects in them and won't fit on a single A4 page.

I would like to paste an image of them onto an A3 page in word or ppt so that I can include them in the warehouse documentation.

|||

I had thought of grabbing the graphics and making them into some Visio Shapes, that would allow graphical documentation of packages, but they are not my images to do with as I wish! MS?

|||

I know it seems a little bit of a strange thing to want to do, but I am only building the warehouse and will (hopefully) take no part in the adminstration.

So, in order to provide our DBA with a fighting chance, I want to do my utmost to give as much help as possible as to the inner workings of the packages.