May 14, 2013 at 2:26 am
Hi I am having difficulties with data that has hidden characters.
I have the following and need to transfer this data to a .txt file. The query after the union works fine but the first one just wont pass through to the destination. If I put the query in a variable I get an error that there is invalid characters in the data. Please assist this line definately has to be part of the output
SELECT
'Col1, ' + 'Col2, ' +'Col3, ' +'Col4' as col1
union
SELECT
CAST([Col1] AS CHAR(6)) +
CAST([Col2] AS CHAR(2)) +
CAST([Col3] AS CHAR(4)) +
convert(varchar(7),convert(decimal(18, 2), [Col4]))
FROM tabel1;:crying:
May 14, 2013 at 2:42 am
Please will you provide table DDL for tabel1, a few rows of sample data and the actual error message you get.
Thanks
John
May 14, 2013 at 2:59 am
Hi John,
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)
May 14, 2013 at 3:03 am
Thanks. Please also post the actual query and the actual error message.
John
May 14, 2013 at 3:23 am
Hi John,
Source = OLE DB Source
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)"
May 14, 2013 at 3:31 am
You do indeed have some strange hidded "¬" characters in that first part of the query. I managed to eliminate them by deleting that part and typing it in manually.
John
May 14, 2013 at 3:38 am
Yes, that's exactly where the problem is. Those characters have to be part of the output. I believe they are used as delimeters for the system to which I am sending the file to.
I even tried using the script component but I am unable to export those.
Is there a way I can create a template with that line as header and have my extract start on line 2?
May 14, 2013 at 3:49 am
Is your variable evaluated as an expression? If so, have you tried typing the "¬" characters directly into the expression editor? Another thing you could investigate is whether the data flow or the flat file connection allows you to specify a header row, in which case you could just type it in and it wouldn't need to form part of your query.
John
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply