SQL Server Performance Forum – Threads Archive

new with creating a dts package

we have a textfile that we import in Excel. It’s fixed with, 142 columns. When I import it in excel, it gives me about 34000 rows. But when I import using DTS package that is exported to Access mdb, rows becomes 17000 rows <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> What am I doing wrong? I tried changing things too and now I am getting too many columns and whitespaces error popup, something like this. And another hurdle that I’m faced is that there are rows that contains combined data such as an example, street, city and state. Unfortunately, we can’t modify the source text file. However, I do have a fieldmarker which I used in Excel so do when I import it, I can select the area very close to that marker. Then it creates a line. Here is a sample of my fieldmarker. 12345p1234567p12pp123456p Everytime i see a p, I click it with a mouse to create a vertical line in fixed width importing. In that case, there are 5 columns. Please help. Thanks, V1rt (moved from General DBA section as post relates to dTS)

Uhm, hard to figure out just from what you mentioned. Are you sure you got 142 columns in both cases? What is row delimiter? CR/LF? Try to import into mssql server (or access) as one column row and check how many rows you have. Just to be sure what is correct row count. You edited your post, so I have to do the same. I guess your row is 142 characters long, not 142 columns. I’m not sure why you have problem with fixed length format. Anyway, p letter is not good column separator. Better use vertical pipe ("|") because it is far less probable to have it inside your street name. Then you can use delimited format instead of fixed length.

Nope, it’s really 142 columns. Here is my fieldmarker. I’m going to try | symbol. Btw, the separator should still be a part of the each column in the real data. This is what I use to prepend to the beginning of the real data. 123456789p12345678p12345678901234p1234567890123456p12345p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234p12345678901234p12345678p12345678p12345678901234p1234567890123456p12345p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234p12345678901234p12345678p12345678p12345678901234p1234567890123456p12345p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234p12345678901234p12345678p12345678p12345678901234p1234567890123456p12345p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234567890123456789p123456789012345678901234p12345678901234p12345678p12p1p12pp123456789p123pp1p12345678901234567p1234567890123456789012345678901234567890123456789p1pp1234567p1p123p123456p12345p12345p123456p123456789p1234567p12345p1234567p12345678901p12345678901p12345p1234567p1234567p1234567p1234567p1234567p1234567p12p123456789p1234567p123pp12345678901234567p12345678901234567p12345678901234567p12345678901234567890123456789p1234567890123456789p1p12345678901234p1234p123456789012345678901234p123p123p1234567p123456789p123p12345678901234p1234567890123456p12345678901234p1234567890123456p123456p123456p123456p12p1234567890123456789p123456789p1p12p123456789012345678901234567890123456789p123456789012345678901234567890123456789p12345678901234p1234567890123456p12345678p123456789012345678901234567890123456789p123456789012345678901234567890123456789p12345678901234p1234567890123456p12345678p123456p123456789012345678901234p1p1234567890123456789012345678901234567890123456789p1234567890123456789012345678901p1234567890123456789p1p1234p123456789p1234567890123456789012345678901234567890123456789p1234567890123456789012345678901p1234567890123456789p1p1234p123456789p123456789012345678901234p123456p123456p123p123456789p1p1pp1234567p12345678901p12p1p12345p Then there is a choice in dts wizard wherein we can check if we want to skip first row. If I do that, then I will get an error when I execute the package. It says, too many rows. But i look at the properties of my textfile within wizard, the vertical lines are still intact. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I don’t know why. Any more tips?

Why do you want to skip the first row? Are column names written there?

actually, there are no column names but we do have the file layout. That’s the reason why we came up with that time of fieldmarkers. But we have a separate file which contains all the 142 columns. The reason I checked skip first row is before of the fieldmarker. I don’t want her to be included in the target table.

Ahhh!!! Is this a bug in MSSQL? I tried your suggestion by importing it. In Excel wizard, when I scroll to the very last, the row still looks fine. But in MSSQL’s import task, when I scroll to the very last, the last few columns are messed up. Take note, i’m using the same source file. I tried it in 4 production MSSQL servers, same results in wizard. I tried importing the same file in Access, and everything looks good. What is the fix for this? Thanks, V1rt

Here is an update. I noticed that my rows are shorter than my fieldseparators. I have about 5 more columns that appears as empty. Let say, the total length of the above field separator I posted is 2,050 characters while the real data is just 2,000 characters. This is where the problem starts happening. Unfortunately, MSSQL is unable to handle our future columns but Access and MS Excel can. It doesn’t append empty columns even if it has already reached {CR}{LF}. And what happens next is the the second line gets concatenated to the previous line even if there is a {CR}{LF}. What’s wrong? Any work arounds?

Hey guys, here is better example of how to show the bug I’ve discovered. http://restricted.dyndns.org/dtsimportissue.html

If I got it right, there is no bug. You defined fixed length format with 20+ characters and your next two rows are only half that size.

I know but how come Excel and Acccess is able to import it properly? It’s adding blank spaces to those lines that are short. I also got a reply from the other forum that it’s not a bug. DTS is always expecting that all rows should be in fixed length. This is not nice because what if your data on one of the rows is shorter? We don’t expect all vendors to pad their rows with spaces to match the length. Anyways, I’ll try to figure out the field marker. I hope the other columns are not needed yet. Thanks.

That’s what fixed length format means. You can use CSV format instead.

csv is better but the mainframe folks doesn’t like to cooperate. They don’t want to change their automated script that uploads it to our server. And it’s going to affect other systems too that are loading that same file. Too many dependencies

hi mmarovic, you know what I wish that DTS should have is MS Access’s specs. It allows us to separate data using the table specification we have created. It works well. I wish DTS supported this.

Well, if you need it imported to mssql server db and you don’t know how to do it using dts directly but you know how to import it to access, then do it. After that you can use dts to import from access to mssql server.

Not because I’m new with DTS, it’s because of this Microsoft links. DTS was built that way and they have some work arounds. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> http://support.microsoft.com/default.aspx?scid=kb;en-us;271247</a> http://support.microsoft.com/default.aspx?scid=kb;en-us;247360</a> Looks like it gets the length of the first row then applies that length to all rows. That’s why, lines get wrapped. I don’t know their reason for doing that. And lastly, I don’t want to pad each row with blank spaces just to match the max width of the file since I will be introducing another form of failure even if I’m 99% confident that I will not be screwing up the source text file.

Ok, no offense from my side. As I said, you can use access or excel to import file there and then you can use dts to copy access/excel data to mssql server.

im also having same prob., I think, Fixed length file required all the columns with fixed length including last column..

Hi dishanf, I will have to redo my importing again from DTS. I found an issue with the text file I am importing. It’s got a long header and trailer line that needs to be removed. Btw, I just didn’t find the issue today or last week since I was not able to reply immediately. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> So I guess, from what you are stating, the last column should be in fixed length too. I haven’t checked this kind of data yet assuming this is in the last column: abcdefhijklmnopqrstuvwxyz ILLINOIS60191 abcdefhijklmnopqrstuvwxyz ILLINOIS6019 abcdefhijklmnopqrstuvwxyz ILLINOIS60192 abcdefhijklmnopqrstuvwxyz ILLINOIS60193 Noticed the second line, I’m gonna try it one of this days. Thanks for that info too.

]]>