I have followed the instructions for SSIS Lesson 1 exactly but i get these 4 errors when I come to debug at the "Lookup Date Key" lookup transformation. Last step in the lesson.1. [Lookup Date Key [66]] Error: Row yielded no match during lookup. 2. [Lookup Date Key [66]] Error: The "component "Lookup Date Key" (66)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (68)" 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 Date Key" (66) 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. I have tried this on 2 hardware setups and recreated the package several times on both. The only thing i can think of is that the collations on both servers is SQL_Latin1_CP1_CI_AS and British English (I have huge legacy databases from SQL 7.0 and cannot get my tech support to change language settings of server to UK English). Is it possible that this is causing the lookup failure mentioned above (Q1)? How can I change the collation/language settings within the DTS so that the text file matches the AdventureWorksDW database settings if this is the issue (Q2)?Are the error codes listed anywhere and if not can they be added BOL (Q3)? I have read other threads and they suggest 0xC0209029 means lookup failed due to differing lengths. Can dates have differing lengths (Q4)?

I have done some more searching and found the answer to Q3 ish. Although it doesnt appear in my updated offline BOL (December 2005) this URL http://msdn2.microsoft.com/en-us/library/ms345164.aspx#msgError has mesages. Hopefully this will get more descriptive with time. 0xC0209029 DTS_E_INDUCEDTRANSFORMFAILUREONERRORThis is my particular error and I understand i induced package failure by not permitting more than 0 errors. This may mean the error code is a bit of a wild goose chase. No one else seems to have had trouble with this tutorial which suggests to me that the matching data rows for the lookup do exist in AdventureWorksDW (freshly installed).

After reading my last post i thought wouldnt it be a bit noobish to post wihotu having checked the obvious. "No one else seems to have had trouble with this tutorial which suggests to me that the matching data rows for the lookup do exist in AdventureWorksDW (freshly installed)." well it appears i didnt check! 60 rows dont match up. picking one of the 60 examples 07/01/2001 that doesnt have a lookup it predates all dates in the dbo.DimTime. However the first date in that table is 01/07/2001... yup it all clicks. sample file is MDY my server is DMY. A quick change and voila. Unfortunately not I know get an error about mismatched locale ID's. Looking closer I have got this in my temproary imported table (using import export wizard). 07/09/2001 00:00:0007/10/2001 00:00:0007/11/2001 00:00:0007/12/2001 00:00:0013/07/2001 00:00:0014/07/2001 00:00:0015/07/2001 00:00:0016/07/2001 00:00:00 This suggests SSIS does incosistent imports on a row by row basis!!!!!! Please can someone tell me how to force the correct date format?

I had some problems of that kind before... Jamie blogged about that, too... http://blogs.conchango.com/jamiethomson/archive/2005/04/26/1337.aspx I would import the field as a string (it seams to suggest me that instead of a datetime field when I click on "suggest types", does it do so on your box, too?) and then use a derived column task to convert it to a "correct" date... You can use an expression like (DT_DATE)(SUBSTRING([Column 2],FINDSTRING([Column 2],"/",2) + 1,FINDSTRING([Column 2]," ",1) - FINDSTRING([Column 2],"/",2) - 1) + "-" + RIGHT("0" + SUBSTRING([Column 2],1,FINDSTRING([Column 2],"/",1) - 1),2) + "-" + RIGHT("0" + SUBSTRING([Column 2],FINDSTRING([Column 2],"/",1) + 1,FINDSTRING([Column 2],"/",2) - FINDSTRING([Column 2],"/",1) - 1),2)) for that... It's a little bit complicated just because the format is not fixed (m/d/y instead of mm/dd/yyyy). Also casting a yyyymmdd in SSIS doesn't seam to work, you have to use a yyyy-mm-dd for it... I didn't try using yyyy-m-d (that would make the expression a little bit shorter) and I didn't try it in different language scenarios... I guess you have a non-english installation (language setting, not SQL Server language)?

Thanks Thomas. I do have a 'foreign language'. British English with European date formats!I was praying that i wouldnt have to do a manual string manipulation of date columns on every non-british date file i have to deal with This is the worst case scenario as it is too easy to miss corruption of dates. At least with 2000 DTS you would get a step failure as it wouldn't try and adapt. In my exmaple SSIS has converted the single column to British and US Dates on a row by row basisSurely i can set dateformat MDY somewhere then set it back to DMY?If i worked in 2 sites with different locale ID I would have to do a collation conversion which would be the ideal way to handle this. E.g. set the text file collation to be US English, the DB to UK English and allow SSIS to convert correctly.

If you have any influence on the source data then try to change the date formate to one of the formats Jamie described... Then you don't have to care about that anymore... I'm not sure how to switch date formats so that the data source understands it (I guess not SQL Server but SSIS is the problem)... But if youhave a look at the comments to Jamie's blog you find the commands to do it (which means that it's a SQL Server problem)... I didn't try it, yet, so if you do so please let me know about your success...

...just a little thing to add... I guess that setting the locale (in the source adapter properties) will not change the date behaviour... On the other hand changing the date format will not change the locale (with special characters like if you have "really" international source data...)

The connection manager and component locales affects the way SSIS treats dates in input files. So if the source Connection Manager and Flat File Source has en-US locale, it will correctly read US dates. Then locale of destination Connection Manager & Flat File Destination detemine format they are written to destination file. Note that locales of Flat File Source and Destination should match the locales of appropriate connection manager - this is probably the error you are getting. If a file contains columns in different locales - you will need to read some dates as strings, then use Data Conversion transform to convert strings to dates. Locale of the transform determines how the date is treated, so if you have many columns with different locales you might need several Data Convertion transform.

I had the same errors.After setting all my LocalID's to "English (United States)" the errors were gone. So making sure all the locales are the same is the solution! Thanks guys for helping me out ;)

I had the same problem, my default Locale is English(United Kingdom). The Source File (Flat File Source) date format was therefore set to English(United Kingdom) by default, and running the package gave the same errors you reported. I then changed just the Source File (Flat File Source) Locale to English(United States) and now the package works fine.

How can i check the Locale for the server? I think i have set it all to US and still get the error - is it possible to download this package from somewhere? or email it to me at arvindev@hotmail.com

Thanks Michael,I was having the same error messages and getting a bit frustrated with the whole thing. I changed the locale from Uk to US and it worked - just like that!ThanksOlu

thank you for posting it here. Maybe in other time I may encounter this kind of error. [url=http://www.sqlsteps.com]SQL learning[/url]