DTS Fixed Field length File Limitations

Hi,
I am trying to upload a fixed field text file to a sqlserver table using the DTS wizard. The txt file has 111 columns and the total length of a single row is 5897. The problem is when I use the wizard to specify the starting and ending of each column, its not allowing me to specify the columns beyond the position 4095.
Is there a limitation on this? if so is there a work around ? to solve this.
Any help on this is truly appreciated.

Thanks much. :)I've never encountered this problem but then I have never had a file quite that wide....

Personally what I would do is write a quick wee app or ActiveX Script to slice the file in half and then do the import in two stages... probably doesn't help much but it's the best suggestion I can give you.

DTS fails at customer site with "Too many columns", works locally

I am having the most baffling problem with DTS... :confused:

I have a set of ActiveX transforms that execute on my customers flat transaction data files, destination a single database table. Since they switched to a new method of generating the flat file using SAS, the DTS package mysteriously will fail at a couple select records. The error is always the same, and turning on error logging in DTS yielded this:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Step Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Step Error code: 80043013
Step Error Help File: DTSFFile.hlp
Step Error Help Context ID:0

Step Execution Started: 11/16/2004 6:37:51 PM
Step Execution Completed: 11/16/2004 6:39:39 PM
Total Step Execution Time: 107.415 seconds
Progress count in Step: 515000

The exact same file parses all the way through on my laptop, with the same DTS package. Tests have revealed no strange characters or whitespaces in the data file, not at that record (running a Test... on any of the active x transforms will fail at row 515186 always, until that row is deleted and it fails on some subsequent row - this iteration went on at the customer site until about 5 rows were deleted this month and it finally worked), not at any other records. My database and the customer database are both using the same, default character set.

The only microsoft KB article referencing anything resembling my problem is
http://support.microsoft.com/default.aspx?scid=kb;en-us;292588
but this does not hold because I am not specifying fixed width, but rather comma delimited.

If anyone has any ideas about what other environmental variables are coming into play here, please let me know - I'm at the end of my rope. I believe we are both patched up to SQL 2000 SP3. They have an XP client connecting to a 2003 server; I have an XP client/server. Neither machine has the NLS_LANG environment variable set.This may not be helpful...but have you considered just using a stored procedure instead?|||What happens to that row when you try to import the file into access? If you create an extra column at the top of the flat file, it should insert whatevers in that column for the five offending rows right? Once you get it into a table query it with a NOT NULL. It might give you a clue as to what the offending characters are.

If your stuck with the file then you might just have to use the insertfail phase to make the pump task skip to the next record when it finds an offending row. Read up on multiphase to find out exactly how you'd do this.

Sorry can't help you more.|||Modify the DTS package to use an Execute Process Task and then use bcp.

-or-Use Execute SQL Task and the Bulk Insert Transact-SQL command.|||...the file imports fine here with the exact same DTS package, so I don't want to modify it to address a problem that isn't really the problem. IN other words, there is nothing to indicate there is anything actually wrong with the data itself - no whitespaces, no bad characters or problem causing characters, no datatype mismatch, nothing; it looks just like the last row. Here are the rows before and after as well as the one that failed:

737,10/15/2004,09:11:39,114,15536,1
737,10/15/2004,09:11:49,114,18408,1
737,10/15/2004,09:11:54,714,18024,1

I am not using column 5, but all the others. From last month to this month the number of offending rows increased from 1 to 7, so I don't want to start throwing away data that for all other intensive purposes looks good automatically in case it starts multiplying.

Since it works here but fails there, it has to be something environmental, maybe with character sets or??|||Generating files from SAS...Like from a mainframe?

I betcha you got some low values [CHAR('00') ] going on...

I know you don't want to alter your process, but I ALWAYS create a staging environment and load the data to it, then audit the data to look for problems...then I move the data in after I verify it...

And it's all done with a stored procedure|||Thanks for the tip. I am looking into how these "low values" occur and how these EBCDIC to ASCII conversions can get hung up. I'm sure the answer lies somewhere in there.

Well, the front end application will run a custom DTS package, but not a custom SP. At least the staging need is moot, because it rolls the whole thing back if one record fails...

DTS Error when using text file as destination in DataPump Task

I want to export to a Text File (destination) from a SQL query using a DTS DataPump Task. My query has 28 columns, some how when I try to define the destination columns for my text file the Microsoft Managment Console Crashes completely.

I tough may be my query has two many columns only to find out that it has one to many. If I ramove a column form my query, any column. I get no error at all.

Is there a limit to a Text File destination connection?I got the same problem. I cannot imagine it has anything to do with nr of columns.
Would be interested in solution...

dajm|||I do believe there is a limit to the number of columns but it can't be 28. Still since there's no error besides the crash I have not figured it out yet. Im going to try some test querys.|||I know this isn't what you asked, but I always use bcp for this. Are you doing complex transformations in the data pump? If it is just SQL, you can go to a command line (execute process task if you want DTS to do it) and run bcp "your query" queryout destination.txt -c -T -t "|" (for pipe-delimited). If it is an entire table (or view), you can run bcp "tablename" out destination.txt -c -T -t"|"

DTS error too many columns found

Trying to import a text file with a 11 columns, column delimiter is ;
record delimiter is CR+ LF.
Using a DTS package (bulk copy)
Then I got erros with these discription
------
SYMPTOMS
When you import a text file into Microsoft SQL Server by using the Transform Data task, if no text qualifier is specified and a row with too many columns is encountered, the load of the text file may fail with the following error message:

Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description:Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
Error Help FileTSFFile.hlp
Error Help Context ID:0
The preceding error message repeats in the DTS exception log N times, where N is the value of the Max Error Count setting on the Transform Data tasks options property page.

CAUSE
This problem occurs because of a malformed source text file in which the DTS Data Pump encounters a row in the source file that contains too many columns.

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b300181

--------
I tryed the fix of Microsoft but no result yet.Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.|||Originally posted by Satya
Did you get the specified hotfix from MS Support?
For information refer to thisSQL MAG (http://www.sqlmag.com/Forums/messageview.cfm?catid=11&threadid=7326) thread.

Yes I got the fix en installed but I have still the errors.|||Then better to report back to MS Support.

dts dynamic query & automap columns question

Hello All,

I am sorry if this has been asked - I tried searching but the search kept timing out.

I have designed a DTS package which extracts a query into an excel file.

It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

Currently, the DTS package errors on each attempt because it expects a certain column set.

Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

I would hate to have to set up a different DTS package for each possible column set.

I am sure I am missing something.

Thanks in advance.

- CharlesI would use a sproc with dynamic sql, bcp and xp_cmdshell personally|||Hi Brett,

Thanks for the response. I have to admit I'm fairly new to the more complex aspects of SQLServer (Stored Procedures, cmd_shell, etc). What I have so far was built using the Package Designer - to give you an idea of my level of expertise with DTS. But I'm not opposed to learning if that's what it takes.

Are you aware of any other "simpler" options?|||DTS is such a flukey thing...see it's a dangerous drug...you get used to the GUI, the you start to push it...which means you need to start using ActiveX and such.

What happens then is that DTS acts like a cursor and has to affect rows, one by one. Which slows things down dramatically.

If you could give me/us and example of what you are trying to do, I'm sure we can get you an elegant/effecient solution.

Read the sticky at the top of this thread (ok second sticky) and post what it asks for.|||lol Brett - you have described my addiction exactly. It all started so innocently...

I will try as best as possible to meet your requirements.

The Question: How can I get the result set from an ad-hoc query to export to any format supported by DTS?

This question does not deal with any specific tables, data or queries. I will use examples.

User Interface:
A web-based interface allows a user to create a report from a number of factors, including choosing which fields should display. This allows for variations in the number of fields being generated. The user may also choose from a number of formats.

The web application then dynamically assembles the query based on the user's selections.

Example Query A (5 columns / fields in record-set):
select name, address, state, zip_code, phone_number
from clients
where zip_code like '06%'

Example Query B (8 columns / fields in record-set):
select name, address, state, zip_code, phone_number, billing_number, last_invoice_date, billing_cycle
from clients
where zip_code like '06%'

In the DTS Package:
1) Connection 1 is set to work from a query. The query is set using the Dynamic Properties Task. It could use Query A one time, and Query B another.

2) Connection 2 is set as the output file type. For our purposes it will be xls.

3) The transformation between Connection 1 and Connection 2 exports the query results into the xls file.

The Error:
When Query A is exported, the DTS has 5 columns to map. When Query B is exported, it has 8 columns to map. The one DTS package errors out if the number of columns are different than it expects to find. It is not possible to limit users to a specific number of columns.|||How about as a cheap solution for the 5 column solution, just return empty strings for the last three columns, and that way all of the calls will return 8 columns, but in Excel, it will show as 5.

Will that work for you?|||OK, this is what I would do...

#1. Make sure all access to the database is done through strored procedures
#2. Dynamically build a view based on your users requirements. make it so it's a single column concatenated as a tab or comma delimited and you can even add a headr
#3. bcp out the view with xp_cmdshell

Let me work up a sample|||Wow Brett! Thanks, I'm intruiged. I can't wait to see it.

Thanks again.

- Charles|||What form do you collect the requirements in? Do formulate the query in the front end? Sounds like you do...|||Hi Brett,

You are correct the query if built first in ColdFusion. The user interface is a form through which the fields and associations are made.

The query is created, then saved in a sql table, then loaded as a dynamic property at runtime.

Thanks again,

- Charles

DTS Copy Columns

I am using a DTS to copy columns from one database to another.
How do I do the same thing, but first check if the data alreday exists in the destination table and then only copy if it doesn't exist?
thanks in Advance
ActionAntIN your transformation

Move your data using a query that will return only what to insert.

DTS - Excel conversion from Number to Database Char

I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.

Thanks,
PauloDoes the table already exists? Or are you letting DTS create it?|||The Table already exists. It's created each time the DTS run.

This is the script used on the DTS:
/*
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Temp_freqnib]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Temp_freqnib]
GO

CREATE TABLE [dbo].[Temp_freqnib] (
[Cartao] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[numX] [char] (21) COLLATE Latin1_General_CI_AS NOT NULL ,
[desc] [varchar] (80) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
*/

It must be imported to the numX field.

Thanks,
Paulo|||my mistake...

The table exists only when the DTS runs, and it's created before the import of the data from excel.

Paulo

DT_NTEXT pass through columns in fuzzy lookup transformation

The documentation on the fuzzy lookup transform mentions that only columns of type DT_WSTR and DT_STR can be used in fuzzy matching. I interpreted this as meaning that you could not create a mapping between an input column of type DT_NTEXT and a column from the reference table. I assumed that you could still have a DT_NTEXT column as part of the input and mark this as a pass through column so that it's value could be inserted in the destination, together with the result of the lookup operation. Apparently this is not the case. Validation fails with the following message: 'The data type of column 'fieldname' is not supported.' First, I'd like to confirm that this is really the case and that I have not misinterpreted this limitation.

Finally, given the following situation

- A data source with input columns

Field_A DT_STR
Field_B DT_NTEXT

- A fuzzy lookup is used to match Field_A to a row in the reference table and obtain Field_C.

- Finally, Field_B and Field_C must be inserted into the destination.

Can anyone suggest how this could be achieved?

Fernando Tubio

One possible workaround is using a multicast transform to route the input columns around the lookup transform. A merge join transform can then be used to join the outputs from the multicast and the fuzzy lookup to include the DT_NTEXT field back into the data flow.

I've tried this solution and it works but I wonder if it is really necessary to resort to all these contortions.

|||

It looks like your workaround is the best approach. The Fuzzy lookup does not support DT_NTEXT, DT_TEXT, or DT_IMAGE columns as copy columns OR pass-through columns. I am not sure why that is, but I will try to find out.

Mark

|||

I guess one of the reasons was performance, and that these columns require special handling. You might want to put in a request for this feature for a future release.

Thanks
Mark

|||

Thank you Mark.

Considering my limited knowledge about the inner workings of the data flow pipeline I am very likely wrong, but I would have guessed that a pass-through operation merely involved copying some pointers around. In any case, the package creator can control which columns to pass-through and if he is concerned with performance, then he is in a better position to decide whether to include these columns in the output. So I guess it would be nice to have this choice in a future release.

Fernando Tubio