SSIS to FlatFile

Question

Post reply

SSIS to FlatFile

b_boy

SSCertifiable

Points: 5145
More actions
October 28, 2008 at 4:28 am

#124114

Hello I have a query that generates a result set in SMS, but when i use that query within a SSIS package to create a flatfile, there are no results?
is there anything wrong, because I am confused.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

hodgy SSCertifiable Points: 5906 More actions · Answer 1

can you give an example of the query you are running?

tom

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 2

its a bespoke query, have you come across this problem before?

hodgy SSCertifiable Points: 5906 More actions · Answer 3

it's difficult to know without being able to see the sql itself.

tom

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 4

select 'SKU,A,' + dbo.fn_digit(ItemID) + ',' + dbo.fn_digit(ItemID)+ ',' +

SUBSTRING(Description, 1, 40) + ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,' + CID + ',

' + SID + ',,,,,,,' + CAST([Cost Price] As nVarChar(32)) + ',T,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,'

FROM items where storeexport = 0

hodgy SSCertifiable Points: 5906 More actions · Answer 5

are you hoping to make a comma separated file?

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 6

b_boy

SSCertifiable

Points: 5145

October 28, 2008 at 5:31 am

#890509

thats right in a carraiage return unix format

hodgy SSCertifiable Points: 5906 More actions · Answer 7

is the problem that the file you create is empty? have you check the data coming from the source using a data viewer?

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 8

Thats right, the file created is empty, but when I preview it, i can see data.

hodgy SSCertifiable Points: 5906 More actions · Answer 9

when you say you preview the query, is that in SSMS or using a data viewer in the data flow? (apologies if these questions seem basic or pointless, but sometimes just working through the basic questions can help unlock the answer).

Tom

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 10

b_boy

SSCertifiable

Points: 5145

October 28, 2008 at 5:58 am

#890522

when using data viewer in data flow

hodgy SSCertifiable Points: 5906 More actions · Answer 11

so that would imply that the query is working fine, but there is some problem writing to the file.

could you change the query and replace the single column with all the commas with separate columns using nulls or empty strings to create the dummy columns you require? then map these columns to individual columns in the file and let the connection manager deal with field and line separators?

Life: it twists and turns like a twisty turny thing

b_boy SSCertifiable Points: 5145 More actions · Answer 12

Will this create the CR UNIX csv file format? and besides that column heads are not to exist within the file.

hodgy SSCertifiable Points: 5906 More actions · Answer 13

unfortunately, i don't have any experience in creating unix files so this might be where i sign off.

i do know that you can switch column headers on or off in the connection manager (in the general tab there is a check box to set whether there are "column names in the first data row". also, you can set a file as unicode and also specify the row delimiter.

tom

Life: it twists and turns like a twisty turny thing