May 13, 2013 at 7:56 am
Hi,
I am having trouble exporting data to a flat file (.txt) because the fist line has special characters.
SELECT
'StaffCat, ' + 'EXCONUM, ' +'UNIT Number, ' +'Staffcount' as col1
I get an error "invalid characters in the data"
Please assist
May 13, 2013 at 8:26 am
I had no problem executing that query and seeing the result.
This is the what I copied from your post.
SELECT
'StaffCat, ' + 'EXCONUM, ' +'UNIT Number, ' +'Staffcount' as col1
There are some hidden characters that are not showing.
This is the result:
StaffCat, EXCONUM, UNIT Number, Staffcount
I would try to TRIM each side and maybe convert it to varchar and see if that will make a difference. That is all I can do without seeing the actual data, and not being able to duplicate the error
Andrew SQLDBA
May 13, 2013 at 11:02 pm
Hi Andrew,
Sorry I wasn't clear in my question. When I process that statement through SSIS, the OLEDB source executes successfully but the result is not sent to the destination
May 14, 2013 at 3:37 am
Trybbe (5/13/2013)
Hi Andrew,Sorry I wasn't clear in my question. When I process that statement through SSIS, the OLEDB source executes successfully but the result is not sent to the destination
What is the datatype of col1 and what is the encoding of the flat file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2013 at 3:43 am
Hi, here's the full detail
CREATE TABLE [dbo].[Table1](
[Category] [nvarchar](max) NULL,
[EXCO] [nvarchar](max) NULL,
[UNIT Number] [float] NULL,
[Staffcount] [float] NULL
)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('AD', '08', 4514, 60)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('AD', '08', 8541, 9)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('CC', '09', 1234, 14)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('CC', '09', 9526, 21)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('BC', '07', 7765, 23)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('BC', '07', 3232, 12)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('RT', '02', 8458, 5)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('RT', '02', 4587, 2)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('EC', '11', 6321, 3)
Insert Into Table1 ([Category],[EXCO],[UNIT Number], [Staffcount])
Values ('EC', '11', 7854, 15)
Query =
SELECT
'Category, ' + 'EXCO, ' +'UNIT Number, ' +'Staffcount' as col1
union
SELECT
CAST([Category] AS CHAR(6)) +
CAST([EXCO] AS CHAR(2)) +
CAST([UNIT Number] AS CHAR(4)) +
convert(varchar(7),convert(decimal(18, 2), [Staffcount]))
FROM[Table1];
Destination = Flat File .txt
When I execute query from OLE DB source, the data flow task indicates that it ran successfully (green) but the the items inside dont change and there is no output.
when I try to run the query from a variable i get the following error,
"an invalid character was found in text content. (msxml6.dll)"
The main prolem is the special characters in the first select statement.
May 14, 2013 at 3:47 am
I'll rephrase my question:
what is the datatype of col1 in SSIS (DT_STR or DT_WSTR?) and what is the encoding of the flat file you configured in the flat file connection manager? (unicode, ascii, utf-8, ...)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 14, 2013 at 3:58 am
Hi,
Col1 is DT_STR
flat file = 1252 (Ansi - latin I),
Delimited
Header row delimiter {CR}{LF}
non-Unicode
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply