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 file connection manager not work offline?

Hi there

I have a connectin manager for file( New File Connection..) along with some oledb connection managers.

Now If a put SSIS work Offline as True (I need this for some reason). Connection manager for File gives error as something --

"Connection manager for File connection failed with error code-0xc0014019,Connections cannot acquire connections while work offline is true."

But other connections of olddb work fine.

so do we have to change some more settings with file connection or with file connection we can't work offline?

Thanks and Regards

Rahul Kumar

logtorahul wrote:

"Connection manager for File connection failed with error code-0xc0014019,Connections cannot acquire connections while work offline is true."

Rahul,

The answer seems to be in the error message itself. I don't know your reason for working offline; but when I use it I do not expect any of the connections to work. I guess that option was put there to allow you to open packages while 'no-online' without receiving error messages in your connections.

|||

Yes Rafael,

You are right when working offline we donot expect connections to work,even i do not.

But any other connection manager does not give you this error when we run the package.

Let me explain with a scenario:

Suppose we have a oledb connection ( for our source and destination DB) and a file connection for our log file and all the connection string is being provided by package configuration file.Given work offline as true and delayValidation is also true.

Now if we run the package, all other connection managers pick connection string from the package config file but the file connection gives error as

"it cannot acquire connection while working offline."

Now this sounds little wired to me as all the connection manager can acquire connection while working offline and file connection cannot.

Does anyone use SSIS for database schema maintenance?

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.

We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.

Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.

ANY information at all would be extremely appreciated!On Feb 28, 2:54 pm, "Ben" <vanev...@.gmail.comwrote:

Quote:

Originally Posted by

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
>
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
>
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
>
ANY information at all would be extremely appreciated!

Hi Ben,

There is a rock solid change management process for SQL Server
2000/2005 and it is provided by the DB Ghost toolset from
Innovartis.

The essence of the process is that you script out all the database
objects and lookup (static) data into individual CREATE / INSERT
scripts and put them under source control. The whole dev team then
checks these files out, makes the required changes to the CREATE
statements and checks them back in again (this can scale to thousand
of developers). Once you're ready to release the schema to the test
environment you use the DB Ghost Change Manager tool to make the
target database match the set of source scripts. If, for example, a
developer added a column to a table CREATE script then the Change
Manager would detect this and add the column to the target database
seamlessly.

Basically, DB Ghost enables you to develop in the same way as you do
for a greenfield (release 1) database for every subsequent release of
your schema without losing any data in the target database.

Our customers rave about DB Ghost and can't believe the cost savings
it brings - have a look for yourself :)

www.dbghost.com
Kind regards,

Malcolm

Does anyone knows what is needed in each PC for that?

Hi everyone,

I’m pursuing a fast deployment for SSIS. I mean, we’ve got ten ASP/VB6 developers which often doing some DTS stuff by mean, of course, Enterprise Manager.

Now, they’d have to handle the same but with SSIS. I don’t have very clear whether only is necessary install them SSIS component or it’d requires something else. Framework 2.0 too?

If they are building packages then they will need BIDS installed. If they want to run them on their client machines from outside BIDS then they will need SSIS installed (which is a different install option on the CD).

Framework 2.0 is required but that will be installed off the SQL Server CD automatically if it is needed (I think).

Does that answer the question?

-Jamie

|||

Hi Enric,

Please browse through "Installing SQL Server Integration Services by Using Setup " @. http://msdn2.microsoft.com/en-us/library/ms143510.aspx were they have detailed on the different components of SQL Server Install and their applications. and http://msdn2.microsoft.com/en-us/library/ms143731.aspx (has got information on "Installing Integration Services Side-By-Side with SQL Server 20000 Data Transformation Services)

.NET Framework 2.0 is a must requirement.

Thanks,
Loonysan

|||

Jamie,

Yeah, that asnwer partially my question because I don't know if is needed a license per place or not.

Loonysan,

Thanks for that link.

|||

Do you have an MSDN subscription? I think that covers you to have Developer Edition on each machine.

-Jamie

|||

yes, we have. Thanks.

something else. When people talks about 'Client Tools' for Sql Server 2005 are they talking about BIDS environment and SSIS component generally speaking?

|||

Well i guess it depends who you're asking. When I talk about client tools I mean SSMS and BIDS (which includes the SSIS Designer). I do not mean SSIS. There is a distinction here.

-Jamie

|||OK

Does 2008 will offer a "between lookup"?

Hi,

does SSIS 2008 will finally offer a "between lookup"?

the current lookup support only the equal (=) operator, but when we play with Type 2 SCD dimensions, its important to also test the dates using a between.

or better, the operator used in the comparison should be an option instead of a equal only.

Thanks.

You can already do such lookup in SSIS 2005 using Partial or None cache Lookup mode.

Neither SSIS 2005, nor SSIS 2008 support such lookups in Full cache mode.

|||

but its a row by row process and everytime the system execute the query against the lookup database.

with million of rows and more to load , this is not efficient.

at least, do you plan to improve the lookup component?

|||

Willgart1 wrote:

but its a row by row process and everytime the system execute the query against the lookup database.

with million of rows and more to load , this is not efficient.

at least, do you plan to improve the lookup component?

Hi Willgart1,

There has been no announcement of changes to the lookup functionality in SSIS2008. Announcements on new functionality are not being made until Microsoft can be absolutely sure that that new functionality will make it - in other words they are announcing stuff after they've built it rather than before. Hence, because there has been no announcement doesn't mean there won't be anything. I'm keeping my fingers crossed on improvements to LOOKUP.

-Jamie

|||

Alberto Ferrari wrote a Range Lookup component that we presented at an italian conference last month (www.sqlconference.it).

In the next days we should finish a little documentation of the component and we will publish it on www.sqlbi.eu

It works in memory and is pretty flexible in the way you can configure the match expression.

Just a few days... if you subscribe to the newsletter on www.sqlbi.eu you'll receive an update when it'll be available, otherwise you can check our blogs (http://www.sqljunkies.com/weblog/aferrari/ and http://sqlblog.com/blogs/marco_russo/).

Marco Russo

http://www.sqlbi.eu

http://sqlblog.com/blogs/marco_russo

Does 2008 will offer a "between lookup"?

Hi,

does SSIS 2008 will finally offer a "between lookup"?

the current lookup support only the equal (=) operator, but when we play with Type 2 SCD dimensions, its important to also test the dates using a between.

or better, the operator used in the comparison should be an option instead of a equal only.

Thanks.

You can already do such lookup in SSIS 2005 using Partial or None cache Lookup mode.

Neither SSIS 2005, nor SSIS 2008 support such lookups in Full cache mode.

|||

but its a row by row process and everytime the system execute the query against the lookup database.

with million of rows and more to load , this is not efficient.

at least, do you plan to improve the lookup component?

|||

Willgart1 wrote:

but its a row by row process and everytime the system execute the query against the lookup database.

with million of rows and more to load , this is not efficient.

at least, do you plan to improve the lookup component?

Hi Willgart1,

There has been no announcement of changes to the lookup functionality in SSIS2008. Announcements on new functionality are not being made until Microsoft can be absolutely sure that that new functionality will make it - in other words they are announcing stuff after they've built it rather than before. Hence, because there has been no announcement doesn't mean there won't be anything. I'm keeping my fingers crossed on improvements to LOOKUP.

-Jamie

|||

Alberto Ferrari wrote a Range Lookup component that we presented at an italian conference last month (www.sqlconference.it).

In the next days we should finish a little documentation of the component and we will publish it on www.sqlbi.eu

It works in memory and is pretty flexible in the way you can configure the match expression.

Just a few days... if you subscribe to the newsletter on www.sqlbi.eu you'll receive an update when it'll be available, otherwise you can check our blogs (http://www.sqljunkies.com/weblog/aferrari/ and http://sqlblog.com/blogs/marco_russo/).

Marco Russo

http://www.sqlbi.eu

http://sqlblog.com/blogs/marco_russo

Documentation

Hello,

I have a BI solution in SQL server 2005, is there a documentation tool to help me do the documentation for my project?

I use SSIS, SSAS and SSRS. I need documentation for the first two.

Thank you

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisvcs.mspx - SSIS

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssasvcs.mspx - SSAS

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/rptsvcs.mspx - SSRS

And SQL server 2005 books online too (updated Fe2007).

|||

Sorry, i must have explained wrongly.

I want to generate my solutions documentation, is there a way to do it using sql server 2005, or do i need a external tool?

If so, what tool?

Thanks

Do you need to be a programmer to use SSIS?

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

If i understand your question correctly, the Script Component may be overkill. You could just drag over the OLE DB Dest and Source. Between the two items in the data flow, you could use a Derrived Column transform or Data Conversion based on what the nuts and bolts of your data conversion. It looks like you just want to do a conversion by your example and you could do that in the Data Conversion transform.

-- Brian

|||

Worf wrote:

Hello

I've been looking for SSIS examples where you need to import data from a OLE DB datasource and export it to a OLE DB destination source whilst the data in a colum is being transformed with a simple SQL statement.

For example:

SELECT (CONVERT(datetime, Left(Counterdatetime, 23))) datum
FROM CounterData

It seems to me that you have to use the Script Task Component ans start coding in Visual Basic.Net (or whatever)

Am I missing something here or is it normal you have to be a programmer to do simple thins like that?

Many thanks!

Absolutely not. You have already done the required transformation in your SQL statement above. If you can do your required transforms in the source then invariably that is what you should do - there's no point in having unnecassary transformations in your data-flow. If you wanted to carry out those same transformations in the data-flow then you would require a Data Conversion component (or possibly the Derived Column component) and there is no "programming" (in the common sense of the word) involved in using those.

-Jamie

|||Good call Jaimie.