A thing which usually takes 5 minutes (for a junior SQL resource using SQL 2000) with either Data Import tool or DTS now takes over 3 hours with a skilled SQL developer with SSIS. Even a business user is able to import such a PSV file with 70 columns and few thousand rows into Excel in absolutely no time. There are tons of errors - one after the other that the developer has to fix before the import happens - one place or the other. No way to just simply drag and drop and be done with it like before. We have decided to leave a blank DTS package with backward compatibility mode always on the 2008 server to do such tasks and it really saves us a lot of time. Anybody here to challenge SSIS or the new SQL 2008 R2 data importer is the way to load a PSV file vs a DTS importer? Its very very disappointing to see how the improvements on a product have increased the development time on projects dramatically.Chandra Sekhar

Sounds like you are doing something wrong. It is no mroe difficult to import a PSV in SSIS than it was in DTSChuck Pedretti | Magenic North Region | magenic.com

LOL. Just try importing this simple data below into SQL Server through SSIS you will know.. You may just assign all the columns as varchar(1000) with any default column names, and still you won't be able to get it to import without tweaking something in the Importer. And after that, try with either DTS or through Excel it will be a piece of cake. All you would do there is specify the separator that's all everything else will be taken care of by itself. 1|en-US||16630 Redmond Way|WA|USA|||Redmond|98052|||16630 Redmond Way|WA|United States||16630 Redmond Way, Redmond, WA 98052-4434|Redmond|98052-4434||47.673302|-122.118576|47.673099|-122.11871|High|16630 Redmond Way, Redmond, WA 98052-4434|Address|Success||| 2|en-US||16552 NE 74th St|WA||||Redmond|||High|16552 NE 74th St|WA|United States||16552 NE 74th St, Redmond, WA 98052-7804|Redmond|98052-7804||||47.670211|-122.119581|High|16552 NE 74th St, Redmond, WA 98052-7804|Address|Success||| 3|en-US|Seattle Space Needle|||||||||||Washington|United States||Space Needle, WA|Seattle|||47.620495|-122.34931|||High|Space Needle, WA|LandmarkBuilding|Success||| 5|en-US||W Jefferson Blvd|CA||||Los Angeles|90007|||W Jefferson Blvd|CA|United States||W Jefferson Blvd, Los Angeles, CA 90007|Los Angeles|90007||||34.0236140484618|-118.28398661223|High|W Jefferson Blvd, Los Angeles, CA 90007|RoadBlock|Success||| 6|en-US|||CA||||Los angeles|||||California|United States||Los Angeles, CA|Los Angeles|||34.0532901138067|-118.245009407401|||High|Los Angeles, CA|PopulatedPlace|Success||| 7|en-ca|Montreal Canada|||||||||||Quebec|Canada||Montreal, QC|Montreal|||45.5122858285904|-73.5543867945671|||High|Montreal, QC|PopulatedPlace|Success||| 8|en-CA||444 Kirkpatrick Cres NW|AB|Canada|||Edmonton||||444 Kirkpatrick Crescent NW|AB|Canada||444 Kirkpatrick Crescent NW, Edmonton, AB T6L|Edmonton|T6L||||53.4802172766598|-113.403092450204|High|444 Kirkpatrick Crescent NW, Edmonton, AB T6L|Address|Success||| 9|en-gb|BD4 9JB||||||||||||United Kingdom||BD4 9JB, United Kingdom||BD4 9JB||53.7784838676453|-1.71956151723862|||High|BD4 9JB, United Kingdom|Postcode1|Success||| 10||||||||||||16630 Redmond Way|WA|United States||16630 Redmond Way, Redmond, WA 98052-4434|Redmond|98052-4434||47.673302|-122.118576|||High|16630 Redmond Way, Redmond, WA 98052-4434|Address|Success||47.673099|-122.11871 11||||||||||||457 Kirkpatrick Crescent NW|Alberta|Canada||457 Kirkpatrick Crescent NW, Edmonton Alberta, Canada|Edmonton|T6L||||53.4802068024874|-113.403086364269|Medium|457 Kirkpatrick Crescent NW, Edmonton Alberta, Canada|Address|Success||53.48021728|-113.4030925 12||||||||||||Fern Street|England|United Kingdom||Fern Street, Bradford BD4 9, United Kingdom|Bradford|BD4 9||||53.7784677743912|-1.71975195407867|Medium|Fern Street, Bradford BD4 9, United Kingdom|Address|Success||53.77848387|-1.719561517Chandra Sekhar

Chandra, if you could specify the exact challenges you encountered or at least the errors you experienced that would be helpful. My guess, the Data Import Export Wizard gave you truncation errors, is that right? Besides, Excel has data file import capabilities, why would you use DTS/SSIS to do that instead?Arthur My Blog

In my humble opinion, this kind of crude text imports/exports are easier with bcp. Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

Just did it in under 2 minutes. Used the import wizard and the only thing I had to do was to set the column sizes. Default is 50 characters, selected them all and set to 255 in one place. Normally you would do some data analysis and set up your column sizes the the required value. I think the only difference that you are noticing is that the default is now 50 char instead of 255(DTS)Chuck Pedretti | Magenic North Region | magenic.com

I agree this file didn't have the extra line breaks at each row and that's why it worked seamlessly for you. But the one we are handling has few rows with blank lines and SSIS can't handle it. It needs every row to have some value - whereas DTS didn't care - if it found blank row, it just inserted a blank row in the table. And yes - Sebastian - bcp is the way to go for bulk imports from files. I totally agree. The SQL account I had on the server didn't let me run a bcp command. I'm not exaggerating. We were doing a one time geocoding through Microsoft's batch geocode process which gives out PSV output with over 100,000 rows with several columns in the results. When we tried to use the data importer in SSIS, since the default field size is 50 chars, it was complaining about truncation so I increased to a 1000. Again it fails with a truncation error. There was no where to specify ignore blank rows - is there a place I missed? Any ways, the point is, what ever was so simple to use with a self intuitive UI for anyone to use is now complicated with SSIS introduction. My opinion.Chandra Sekhar

A blank line in a delimited file is an error, DTS is doing it wrong. Chuck Pedretti | Magenic North Region | magenic.com

Hi, The following T-SQL command (BULK INSERT) offers the same functionality as bcp http://msdn.microsoft.com/en-us/library/ms188365.aspxSebastian Sajaroff Senior DBA Pharmacies Jean Coutu

A blank line in a delimited file is an error, DTS is doing it wrong. Chuck Pedretti | Magenic North Region | magenic.com Tell that to Microsoft Bing people. Look at their output file for geocode requests ;) In such a case, what would you do? Would you rather search replace the blank lines before feeding it into the Import prrocess? If DTS did it wrong, I'm okay with it. It works for me. Same case with Microsoft Excel - all versions of Excel works just fine.Chandra Sekhar

Hi, The following T-SQL command (BULK INSERT) offers the same functionality as bcp http://msdn.microsoft.com/en-us/library/ms188365.aspx Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu Yes I did look at that as well. But the user account I had access to didn't have enough permission to create dynamic linked server through query to open a file.Chandra Sekhar

Chandra, if you could specify the exact challenges you encountered or at least the errors you experienced that would be helpful. My guess, the Data Import Export Wizard gave you truncation errors, is that right? Besides, Excel has data file import capabilities, why would you use DTS/SSIS to do that instead? Arthur My Blog Yes the Importer tool gave me Truncation errors because there were several lines which were blank. I have solved my problem by using SQL DTS backward compatibility tool to extract the file into a table. The goal was not to load the data into Excel - but to get it into a table for a data warehousing need. And, when a business user asks a technical person why it takes so long to load a file into a table while he could do it in no time in Excel - I would think its appropriate to give that business user an acceptable answer. Chandra Sekhar

One possible solution here would be to load the file as a text file source and run it through a script task. Within the script task, you can simply use the Split() method of the string data type while defining the delimiter and get an array of strings in return. Then, you can do whatever you want with them.

One possible solution here would be to load the file as a text file source and run it through a script task. Within the script task, you can simply use the Split() method of the string data type while defining the delimiter and get an array of strings in return. Then, you can do whatever you want with them. Wow! T-SQL has a split method?? I would rather stick with being within SQL server itself for the data processing and not include any .NET scripting.Chandra Sekhar

Hi Chandra Sekhar, T-SQL does not has split() method, but you can split string by T-SQL like following: http://www.rad.pasfu.com/index.php?/archives/56-T-SQL-Split-Function-Split-Field-By-a-Delimiter.html If you do like using T-SQL to achieve your target, I suggest you can post the question to Transact-SQL forum: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads Thanks. Eileen