SQL2005 equivalent of the data import task in SQL2000

SQL2005 equivalent of the data import task in SQL2000

• Surfsune

  Here are the specifics we need to know ...

  Firstly, do you expect to see truncation at all Are your destination columns the right size to accept your source data If they are - and you hint at that - then it is quite likely that column sizes in the SSIS data flow are different from what you expect. Remember that there are columns at the source, columns within our data pipeline, and columns at the destination.

  So, where are you seeing the truncation errors - at the source or at the destination

  If at the source, then the data being read in from your file is in some cases larger than the column you have defined for it in the flat file connection manager - data is being truncated as it is read from the source into our pipeline. You can either change the size of the column in the flat file connection manager, or set error handling on the flat file source adapter to ignore truncation errors. Do the former if you do not want truncation: choose the latter if you expect these are just padded fields that can be truncated with impunity.

  If truncation is at the destination, then it is because columns in our pipeline (the columns you defined in the flat file connection manager) are larger than those in your destination. Again, you could set the column sizes appropriately, or set error handling to ignore errors.

  You may also want to have a look at the following short webcast, where a dashingly handsome presenter shows how to build your first flat file to sql server import package, including setting column lengths ...

  http://g.msn.com/9SE/1 http://msdn.microsoft.com/msdntv/episode.aspx xml=episodes/en/20050505sqlserverdf/manifest.xml&&DI=6259&IG=2a65598a1a424c80aa83480e4f63324c&POS=1&CM=WPU&CE=1&CS=OTH&SR=1

  For security issues we really need more detail on your security architecture and the errors you are seeing.

  Donald

  
  
  

• rsk7

  Thanks.

  >I checked the field definitions and updated those to what they should be (two of the fields were longer than the standard 50 nvarchar length).

  So your first errors were truncation errors This would certainly be the right way to solve these, so long as the destination table can still accept the data without truncation

  >I saved the package to a file and executed it. It kept failing.

  What errors did you see this time If truncation, then it could be at the destination. Or, if a data conversion component is involved, it could be there - does either the destination or the data conversion component have a warning triangle

  >The error message led me to change the action that the data conversion process was set to do by default. I changed the fail component to redirect on error (although I have been unable to insert a branch to an error file yet) and ignore error on truncation. I managed to load about 10% of the data this way (+-200000 records).

  So about 90% of the data is being truncated Here's what I think is happening ...

  Originally you were perhaps getting truncation at the data source. Fixing the field lengths corrected that. However, the data conversion component was still using it's original conversions which, if ANSI-Unicode, or some type to string, will specifiy the length of the string to be cast to. The expected length to result from the conversion can be modified to ensure that truncation does not occur during conversion.

  >The truncation seem to happen at the destination table.

  If you think this is the case, then it is possible that the length to which data is being converted by the data conversion component is longer than the database expects.

  You can check the metadata of the data flow at any point by double-clicking the path between two components. In the Path Editor you can view the metadata for all columns which will allow you to track down exactly what metadata is expected at any point.

  However, one of your posts indicated that there also conversion errors - which may be the result of invalid data or an inappropriate type conversion, somewhere along the line.

  Donald

  
  
  

• jizhang

  Hi Jamie,

  I have an extremely simple task I am trying to achieve. Read a text file and load into a table. I created a dtsx package that consists of three control flow tasks:

  Drop table task

  Preparation task

  Data flow task

  The dataflow tasks are as simple:

  Define source: a text file

  Convert

  Define destination: a table

  I have already made several changes to the package. It seems it is essentially complaining about the input and output structures not being the same (string lengths, etc). This was never a real issue in 2000. As long as the data types could implicitly be converted and the field lengths were OK it would run. If field lengths were not OK there would be warnings of truncation. I have now rigorously applied the same definition to the input and output files. Another issue was the fact that the text file was sitting on the server and my datamart on my laptop. That had some security and access issues, even though I had full access to both systems' drives and can manipulate the files. These issues I have not been able to solve directly. I therefore copied the table to my laptop drive and that issue went away. Now I get the following error:

  Description: The "output column ""ID_SKU"" (97)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column ""ID_SKU"" (97)" specifies failure on error. An error occurred on the specified object of the specified comp

  I will now check to see what triggered this specific error. The help file is not totally forthcoming if I enter the error code so that will be an additional expedition on the net to find the cause and hopefully the solution to this error.

  I am quite prepared to go through a learning curve I have been doing that for many years, but sometimes it seems things get over-complicated. And what worked fine is not working anymore.

  
  
  

• Brian Schoenbeck

  > Description: The "output column ""ID_SKU"" (97)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column ""ID_SKU"" (97)" specifies failure on error. An error occurred on the specified object of the specified comp

  That error is becuase the flat file source cannot convert an incoming column to the data type you have specified in the flat file connection manager.

  BTW, you say there is a convert (data conversion ) component in the data flow That is another place where data conversion errors, or data truncation errors can occur - whether or not your source and destination columns are of the correct sizes. You need to ensure that the data coming from the data conversion component is also correctly aligned.

  Is the data conversion component performing some conversion that cannot be done by the Flat File source

  Donald

  
  
  

• James Proctor

  Hi Donald,

  I really appreciate your input and time. I spent another day on this issue and have not really resolved much. The customers' IT crowd has done some test and have essentially confirmed the problem. They have recreated a file where the records with a field length of more than 50 were removed. This ran way past the point it would normally bomb with the wizard. As soon as a I add a record to the table with one field longer than the standard 50 length it would bomb, irrespective of field settings of source, destination and conversion.

  It would also only run some distance if I kept all the fields as a nvarchar(50). As soon as I changed the input parameters of the columns in the wizard to what they should be it bombed. If I used an unsigned 4-byte integer for a INT data type it wouldn't work. If I changed that to an unsigned 8-byte integer (BIGINT in my opinion), presto it would get past that point, but again not in every case.

  Further to this I also did some editing of the raw text data file. The field delimiter was a semi-colon. Some of the fields contained a comma in the field which I removed with vedit (file is 1,3GByte). I also found 53 "unprintable" characters. These I believe were a relict from the mainframe data coding (probably EBCDIC), where the data was originally sourced. These I also removed as I wanted to make sure erroneous data wasn't the cause. However this did nothing to get past the point of breakdown. At this stage I made the error to change the field delimiter to a #. The wizard didn't recognise this and didn't allow me to select any other field delimiter either so I had to change this back to a semi-colon. Does the wizard now run No, it gets to about 250 000 records and then gives me a truncation error for a field that is actually in the source table 1 character long, which I am now writing to a field that is nvarchar(50)!

  I am back trying the SSIS. All conversions and lengths have now been checked and double checked by myself and two others, but no obvious discrepancies have been found. The input, output and conversion components are now all the same. Every field has been set to nvarchar(50), wherever I have seen an error output I have set it to ignore failure on truncation. So far I have still not been able to get the data in. I am really getting to my wits end. The next thing I might do is to uninstall 2005 from one of my boxes and reinstall 2000. Load the data, detach the database and copy this to my laptop and re-attach to my 2005. Desperation maybe, but it might just work. So far I have spent way too much time that I cannot charge or claim on this issue already. I learned some ins and outs of 2005, but I have a severe dent in my ego and still no solution! ;-)

  Ron

  
  
  

• Snakiej

  Hi Donald,

  Let me add some more detail in how I got to this point. I started off with the import task when you right click on the database name. I went through the options exactly as I have done many times in the past using SQL2000. The file I am importing was an update on a file I had previously imported in SQL2000 without any problems. I tried in vain to get the 2005 package to run without an error. I checked the field definitions and updated those to what they should be (two of the fields were longer than the standard 50 nvarchar length). I saved the package to a file and executed it. It kept failing. There was initially no way I could recover the saved dtsx package, but I managed to figure out that I had to enable the Integration Services.

  I was now able to run the dtsx (actually not run because it kept giving errors) package. All occurrences of the meta-data that I could find I have changed to the table structure that was used for the original table (the data I am using came out of the clients SQL2000 database). The error message led me to change the action that the data conversion process was set to do by default. I changed the fail component to redirect on error (although I have been unable to insert a branch to an error file yet) and ignore error on truncation. I managed to load about 10% of the data this way (+-200000 records). The data conversion component is the one inserted by the wizard and is as far as I am concerned not required as the data is to be witten to the destination table that has the same structure as the source table. The truncation seem to happen at the destination table.

  I have now resorted to loading the file into Access2000 and then import using the wizard again into SQL2005. This works. The upshot is that it is very slow because I had to break the text file first into two part because it was too big and it takes two processes to do what should be a simple single process.

  I am less concerned about the security issues at this stage as I really needed the data on my laptop anyway. The only thing is now I had to copy it to my laptop first using up some more time...

  I don't know if the above sheds any light on my problem. It has just been a very frustrating weekend to do something in SQL2005 that was a cinch in SQL2000. Everything looked the same except the result.

  I accept that I probably waded in without too much looking at manuals ;-)... But on the surface it looked like business as usual!

  
  
  

• alexrjs

  Ronald, contact me offline at DonaldDotFarmerAtMicrosoftDotCom and we'll work on this with you. We can then post results back to the forum.

  It may help if I can see some sample data and the destination table metadata.

  Donald

  
  
  

• Prasanth Menon

  With what specifically Ask some specific questions and people can specific answers.

  DTS was indeed easy to use as long as you knew how to use it. The same is true of SSIS. Trust me about that. There is a learning curve just as there is with any new product.

  -Jamie

  
  
  
• SQL2005 equivalent of the data import task in SQL2000