Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS export variable length records

Author  Topic 

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-06 : 12:19:46
sql server v7 sp2
I have a DTS package which exports data from a table.
This has to be in a dts package due to politics.

I put the data rows into a table with a flag to distinguish them.
The DTS package then exports the data to a fixed field file.
The source of the export is an SP which gets data with the flag set.

If the SP is just
select Data from tbl where flag = 1
then Data is truncated at 256 chars during the export

changing the SP to
select Data01 = substring(Data, 1, 200) ,
Data02 = substring(Data,201,200) ,
Data03 = substring(Data,401,200)
from tbl where flag = 1

gets all the data but I have to define the columns in the export transformations and I end up with a long line - I guess it's padded with spaces to the max length of data.

How can I get the output file data to reflect the length of the record?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-06 : 13:14:30
Anyone?
Even a 'yes I've come across the same problem' would be good.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts
Posted - 2002-11-06 : 13:18:21
Here's an idea.. not a full solution by any means. I'm assuming the data your sending has a key.

select uniquekeyfield, Data = substring(Data, 1, 200),'1'
from tbl where flag = 1
union all
select uniquekeyfield, Data = substring(Data, 201, 200),'2'
from tbl where flag = 1
union all
select uniquekeyfield, Data = substring(Data, 401, 200),'3'
from tbl where flag = 1

this breaks it into 3 seperate entries for each line. The last thing to include is to exapnd the where statement to include a check to make sure the field isn't empty.

select uniquekeyfield, Data = substring(Data, 1, 200),'1'
from tbl where flag = 1 and data <> ''
union all
select uniquekeyfield, Data = substring(Data, 201, 200),'2'
from tbl where flag = 1 and data <> ''
union all
select uniquekeyfield, Data = substring(Data, 401, 200),'3'
from tbl where flag = 1 and data <> ''

just an idea... It may not work for you in this case



-----------------------
SQL isn't just a hobby, It's an addiction
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-06 : 13:31:02
That would split the row up into different lines in the file.

The problem is that dts doesn't seem to allow more than 256 characters per field.
If I split it into different fields then I have to map each field to an output field in the file and I end up with a padded row as I have to make it fixed field to avaoid the column delimitter which isn't alloed to be none.

Does this still apply to v2000?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts
Posted - 2002-11-06 : 13:32:37
I got 2k.. lemme test and find out

-----------------------
SQL isn't just a hobby, It's an addiction
Go to Top of Page

M.E.
Aged Yak Warrior

539 Posts
Posted - 2002-11-06 : 13:38:48
Hmm, looks to truncate at 256... Theres a pain in the @$$. I only ever use it to import.

I'll see what else I can find. Unfortunately breaking it down may be the only way.

-----------------------
SQL isn't just a hobby, It's an addiction
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-06 : 13:44:49
I'll probably be getting rid of it for export and using bcp in a couple of months.
People at this company are very much 'this is new lets play with it' types and get very upset at the thought there might be an easier or better way.

In the mean time I'll just cope with < 256 bytes.
Previously they had a separate dts package for import and export for every file type - currently about 40 - growing to about 300 next year. They thought creating new dts packages was a good design point (although every one I've looked at so far has bugs).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2002-11-06 : 14:22:11
Don't know if this will help.

I just tried using DTS to export fixed-length data, up to 500 characters per column, and it worked fine (SQL Server 2000). I'm not clear on whether you need to export fixed-length or import fixed-length. If you need to have a column delimiter, what if you included a tab or comma in the SELECT statement:

SELECT col1 + ',' AS col1, col2 + ',' AS col2...

Yeah, it's a stupid idea, but it allows you to output in DTS using fixed-length columns for long strings.

Another way to do a comma-separated output is to use ADO to open a recordset normally, then using GetString to turn it into either a comma-separated string, or in conjunction with CONVERT to convert the columns into fixed length char strings. You can then write it out using a FileSystemObject, and you can make it an ActiveX step in a DTS package. And it can be totally dynamic too, since it would all be done in ActiveX script.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-06 : 17:51:57
I'm restricted to dts for a release this week then I can junk it.

It's an export.
I don't want a column delimitter.
The data is in a table and I want to export
select data from tbl where flag = 1

Problem - if I use that as the source and a delimtted column it is ok up to 256 bytes but won't output any more.
I can export
select Data01 = substring(Data, 1, 200) ,
Data02 = substring(Data,201,200) ,
Data03 = substring(Data,401,200)
from tbl where flag = 1

But a delimitted column will put characters in the middle.
Fixed field means copying all the columns in the transformation and will pad to the width of the columns.

I can change the source and do whatever I want to the dts package (I'm considering just calling bcp from it but that might be frowned upon) but the package is callled from an external service so has to be included.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Tim
Starting Member

392 Posts
Posted - 2002-11-06 : 19:40:57
A guess...

There is a setting that limits how many chars SQL Server returns when querying varchar and text columns. 256 rings a bell as a default setting.

Could it be that DTS is respecting the same setting?

I can't remember it'sname offhand but I am sure you know it nigel.

Pretty easy to test... woul dlike to know the answer when you finally figure it out

----
Nancy Davolio: Best looking chick at Northwind 1992-2000
Go to Top of Page

Tim
Starting Member

392 Posts
Posted - 2002-11-06 : 20:47:40
curiosity got the better of me, I tried last suggestion I made but didn't get far. Not togive up though...

I got it to work with SQL2K heres what I did.

Created a table (table1) with a 1024 varchar column (str1024) and populated it with X's apart from last character which was - just so I can see end of line easily.

Then open DTS designer, add SQL connection (source) and (fixed length) Text file (destination).

Add transform data task to these. Used the query setting with "SELECT str1024 from Table1"

Then destination properties I clicked define columns, then execute and took the defaults.

Ran the package and text file has all 1024 characters in it

Then I went back to destination properties on the transform task, changed the size property on the column to 10 and reran package. this time only first ten chars written to text file.

I saved the package to VB to see what the property name is that controls this.

Here is the VB snippet

Set oConnection = goPackage.Connections.New("DTSFlatFile")


	oConnection.ConnectionProperties("Data Source") = "C:\Documents and Settings\tim\My Documents\test.txt"
	oConnection.ConnectionProperties("Mode") = 3
	oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
	oConnection.ConnectionProperties("File Format") = 2
	oConnection.ConnectionProperties("Column Lengths") = "10"
	oConnection.ConnectionProperties("Column Delimiter") = ","
	oConnection.ConnectionProperties("File Type") = 1
	oConnection.ConnectionProperties("Skip Rows") = 0
	oConnection.ConnectionProperties("Text Qualifier") = """"
	oConnection.ConnectionProperties("First Row Column Name") = False
	oConnection.ConnectionProperties("Column Names") = "str1024"
	oConnection.ConnectionProperties("Number of Column") = 1
	oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "1"
	oConnection.ConnectionProperties("Max characters per delimited column") = 255
	oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "0"


looks like this bit is what determines the number of chars exported:

oConnection.ConnectionProperties("Column Lengths") = "10"

try changing that to bigger than 256 in your case, either through designor or vb...

I am guessing the declaration in you sp is driving the value that DTS has used for this property.

hope it helps nigel

I am still interested to know the answer when you find it :-)

----
Nancy Davolio: Best looking chick at Northwind 1992-2000



Edited by - tim on 11/06/2002 21:00:09
Go to Top of Page

Tim
Starting Member

392 Posts
Posted - 2002-11-06 : 20:53:34
just checked ... same results on SQL 7

----
Nancy Davolio: Best looking chick at Northwind 1992-2000
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-07 : 05:01:01
Problem is that fixed length will export just that. You will get a 1024 byte row padded with spaces.
I need to get just the data from the table with no padding.

The
oConnection.ConnectionProperties("Max characters per delimited column") = 255
looks promising though if it's available for v7.
Can't find it in the designer but may be settable in the object model.
I'll give it a go.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-07 : 06:34:30
Max characters per delimited column
doesn't seem to have any affect.

still 255 chars (not 256)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-07 : 06:43:27
Now that I know it truncates to 255 chars I found
Microsoft Knowledge Base Article - Q247527
Which states (fixed v2000 sp2, guess no fix for v7)

When you use the Data Transformation Services (DTS) Export/Import wizard, DTS may truncate column strings that are over 255 characters long if all of the following conditions exist:
The column is a character data type (varchar, char, nvarchar, nchar) and the column length is greater than 255 characters.
DTS exports the column to a text file.
Delimited fields are used for exporting.

Workarounds
Fixed field
Use text datatype
use bcp

Microsoft has confirmed this to be a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Tim
Starting Member

392 Posts
Posted - 2002-11-07 : 08:25:31
yeah when you change from fixed to delimited, you can no longer change the column length properties through the package designor interface.

Looks like your goose is cooked.



----
Nancy Davolio: Best looking chick at Northwind 1992-2000
Go to Top of Page

nr
SQLTeam MVY

12543 Posts
Posted - 2002-11-08 : 11:44:58
Tested it for v2000 sp2 and it works.
Still can't access the property in the designer but I set everything in an activeX script at the start of a package anyway.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site