SSIS to FlatFiles

Check if connectionstring is linked for the same SQL Server that your create the query.
Could you attach the package here to correct it? (Rename to txt)
Regards!

Hi M_Boy - hope things are going good for you ..
Use the Data Flow task in SSIS. I suggest you first build the packge using the Wizard. This will do most of the work for you. Here it is:
Step 1: With a Project open in SSIS BIDS, right click in teh Solution Explorer window on SSIS Packages.
Step 2: Choose your database as the Source
Step 3: Choose Flat File Destination as your destination.
Step 4: Select 'Write a query to specify the data to transfer' and then put your query in the next wizard screen.
Step 5: Configure your flat file.
Step 6: Your done, press Finish.
You may have to experiment with your settings, but this is pretty straight forward. Good Luck.

package-wizard-right-click.bmp
wizard-data-source.bmp
wizard-destination-flat-file.bmp
wizard-query.bmp
wizard-configure-flat-file.bmp

I did excatly that but changed the delimiter to {CR}, and still no results

When you say no results, you mean no data or no file? If no data in the file, check the Query. If you cannot find the created file, it could be the Query does not return results or the file is being written somewhere you can't find? When you run this in BIDS, copy all of the text from the Output window and paste here. Thanks,

The query generates a result, and can be viewed with the data viewer when you want to preview it, but when the file is created there are no results

So you can get to the file after it is created, but it is empty?
Connect on Mappings in the Flat File Desination Editor. What is mapped? Attach a pic if you can.

flat-file-mappings.bmp

M_Boy76,
Try to explain better your problems. You told the problem was with the query, but the problem seems to be the mappings as HoggZilla wrote! :-)
Regards!
Pedro

The query generates one column, which is mapped to one column in the destination file, which is fine, but when the package is executed there are no results within the flatfile.....

How many rows generate the query? (Columns is only one, correct?)
You can see the rows in the dataviewer?
You mapped the source with destination column, correct?

If you can attach here the package I resolve your problem more faster!
regards!
pedro

how can i attach a dtsx package here?

rename to TXT and check the "Attach File"

renamed it but still no able to attach, this is it on notepad
test.txt

Are you sure that the query of OLE DB Source return rows?
Could you add a dataviewer to the link row to destination to confirm? (right-click with mouse in the link row)
Source.JPG

here it is....
testscreenshot.bmp

I highly reccomend you do not try to build the commas into your select, let SSIS do the work for you. Break it out into all the columns. Example

SELECT 'SKU' AS COL1, 'A' AS COL2, dbo.fn_getcheckdigit(StockID) AS COL3,  etc....
If you have a bunch of blank columns, as you do, then write them out as '' AS COL14, '' AS COL15, etc...
Your approach is good if you are trying to create a string in T-SQL but not good if you are trying to create an .csv File in SSIS.
 

Could this be the reason why there is also a line break within the output flatflie?

Probably.

Okay, where there is comma within the query, i will have to replace it with:

Null as COL15, what can i do to ensure that null does appear as a value within the CSV file?

I mean:

Null as COL15, what can i do to ensure that null does not appear as a value within the CSV file?

Since it is going to a file, you can use a zero length char like this: ''

Got the following error:

Error 0xc00470d4:

Paste the new SQL here so I can review. Make sure the SQL runs, preview it. Then use the wizard I described above to create a new package. Don't try to modify the existing package - too many possible variables you might not address. Much much easier to do it again in the wizard.

why you dont store the SQL query inside database as a view or stored procedure?
regards

I have just done it, i think the error came when i trued to change the ANSII to UTF-8, which i reverted back and it worked.

Now trying to view ther CSV file and see if it returned the carriage return csv fomat as required by the other system.

I agree with Pedro, but i need to get the query working well which i believe i have with the help of
HoggZilla, once that is done i will then need to automate it to run every week.

Also can i add a sql task to update a column from 0 to 1, so that when next the package is excuted it extracts rows flagged 0 and not 1.

"I agree with Pedro, but i need to get the query working well which i believe i have with the help of
HoggZilla, once that is done i will then need to automate it to run every week." Ok.. we are waiting for feedback! :-)

"Also can i add a sql task to update a column from 0 to 1, so that when next the package is excuted it extracts rows flagged 0 and not 1." Yes... in the controlflow you can do that easy!

Regards!

For the Execute SQL Task, you just need to have a WHERE clause that can identify what rows you want to update. Any chance new rows could be added after you have initiated the process? I have had this type of issue before. Let me explain.
I select some data to write to a file, after I write the file I want to update the rows I wrote so they are not written again in the future. The problem arises when data rows can be added to the table while i am writing. In this case, you need a 3rd stage, like '2', so it is more than a boolean. Before I write I update all rows with 0 to 2. 2 means I am going to write them. In my select I choose all 2's. After I finish I update all 2's to 1's. I don't know if yours is that complicated, but I wanted to offer.

Thats right

ALL items have ExportedToWarehouse = 0, when exported it is flagged to 1. so that when next i excute the packge which has

select * FROM items where ExportedToWarehouse = 0

it will just extract new records flagged 0, and not records flagged 1

Dear M_Boy76,
Do you still have doubts in this subject?
If yes, let me know, if not set this question as answered.
Best Regards,
Pedro

improvements?!

nah , not at all ....a graphical explanation on where to place each task might be handy...

refresh our memory and tell us whta you alredy done...
regards!

I have gotton the part of creating the file but it doesnt appear incremental, as earlier explained, o just gt lost