Importing text File

I am trying to import a text file into Crystal 9. I have used text ODBC drivers, MS Assess type connections. Whatever I try, the first record is always skipped. Crystal is using the 1st record as a field label. The file is a straight ASCII file, 80 characters long per record. There are no field labels.

Any suggestions?You have no choice. All fields need label and CR identifies first record as field label.

Importing Fixed-width txt file - problem

Hi,

I was trying to import a fixed-width file to a sql 2005 table.
The total record lenght is 1500. I was trying to import it to a single column.

The strange thing that's happening is: SSIS is inserting only the first 32 chars of the record and the remaining are gone. I tried using nvarchar(max) and varchar(max) but of no use.
I think something somewhere is going wrong but I was unable to figure it out. Earlier I was able to load a similar file into a single column table.

My Header row delimiter is {CR}{LF}
The preview pane shows the complete record but when it transfers to the table, I'm getting 32 chars only.

Can anybody suggest any ideas to figure this out?

Thanks,
Siva.

Are there NULL characters in your data?|||Yes.........NULLs are there..|||You need to get rid of NULLs. NULLs are string terminators, which is likely why your data "stops" at a specific position on each record.

Your other alternative is to read in the data as binary using a script task, searching for NULLs and replacing them with spaces (or some other valid string character of your choosing). If you write this code, let me know. I need to do just this and haven't had the time to write it.

Importing data from txt file

I have 30 text file with same format that I have to make SSIS package so that I can populate my permanent tables. Because they are just record keeping data we are not ver concerned about validating the data. We just want to copy and dump it in permanent table. Which should mean the least amount of work, because I used one file as a sample, did advance formating to give column names. Now, I did one simple dataflow task that takes the flat file and tranfers the data to OLEDB SQL server database table. Unfortunately, it keeps giving me error. I believe this is because of NULL. I want to take care of this without making staging tables and validating data. Any ideas?

are u running this manually from within your SSIS Package? if so, you should get reasonably good information as to the prob in the Execution Results tab.

Are u sure your incoming ASCII fields are set wide enough to capture the data? If not, truncation errors will kill the process.

Are your table fields setup to accept NULLs?

Seth J Hersh

|||

well, we couldn't find set solution so, as a get arround we used VB.Net script to pad the row to a set width. This seems to have solved the problem for now.

Thank You

Anjali

|||

anj755 wrote:

I have 30 text file with same format that I have to make SSIS package so that I can populate my permanent tables. Because they are just record keeping data we are not ver concerned about validating the data. We just want to copy and dump it in permanent table. Which should mean the least amount of work, because I used one file as a sample, did advance formating to give column names. Now, I did one simple dataflow task that takes the flat file and tranfers the data to OLEDB SQL server database table. Unfortunately, it keeps giving me error. I believe this is because of NULL. I want to take care of this without making staging tables and validating data. Any ideas?

Ummm, it might help if you posted the error. This is a real simple task you are trying to do. (I think.)

importing data

we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).

This method worked ok in DTS2000 and it works with Excel. Any suggestions?

Thank You

You'll need to set up as many columns as you can have in the data. It's tricky because SSIS doesn't handle variable columns very well without implementing some workarounds. (Read in entire row as one field, later split field up using substrings, or other solutions)

Take a look at this forum using the search feature. This has been discussed quite a bit recently.

|||You may try DataDefractor. It handles variable columns quite nicely.

Regards,
Ivan

Ivan Peev | http://www.cozyroc.com/

importing data

we are trying to import data from a flat file using an uptick (`) as a column separator and {CR/LF} as a record terminator. There is a variable number of columns for each record. The initial record in the flat file has 3 columns. Upon processing this record, the import sets all records to 3 columns and does not read the column separators past the second column (even though there may be up to 7 columns in the record).

This method worked ok in DTS2000 and it works with Excel. Any suggestions?

Thank You

You'll need to set up as many columns as you can have in the data. It's tricky because SSIS doesn't handle variable columns very well without implementing some workarounds. (Read in entire row as one field, later split field up using substrings, or other solutions)

Take a look at this forum using the search feature. This has been discussed quite a bit recently.

|||You may try DataDefractor. It handles variable columns quite nicely.

Regards,
Ivan

Ivan Peev | http://www.cozyroc.com/