Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1

problem with XML -> TABLE transfer.

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>

and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null

but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0

Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

You'll have to convert your data to DT_WSTR in the pipeline in order to insert to NVARCHAR.

-Jamie

|||

its already DT_WSTR

Under metadata - the pipeline

1. Name DT_WSTR

2. Address DT_WSTR

3. Age DT_UI1