SSIS Tutorial Lesson 1: Debug Error

SSIS Tutorial Lesson 1: Debug Error

• RNettles

  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
  
  

• manix

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

  
  
  

• John Taylor

  ...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 auo if you have "really" international source data...)
  
  

• Jeff The Super Man

  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.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

   

  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.

  
  
  

• TerrariumNewbie

  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)

  
  
  

• Chandrak Bhavsar

  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:00
  07/10/2001 00:00:00
  07/11/2001 00:00:00
  07/12/2001 00:00:00
  13/07/2001 00:00:00
  14/07/2001 00:00:00
  15/07/2001 00:00:00
  16/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

  
  
  

• rutu

  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.

   

   

  
  

• Jerry K

  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 basis
  
  Surely 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.
  
  
  

• Haseeb Ahmad

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

• Johan Nordin

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

  
  
• SSIS Tutorial Lesson 1: Debug Error