Solved

SSIS to FlatFiles

Posted on 2008-10-28
34
1,189 Views
Last Modified: 2013-11-10
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.
0
Comment
Question by:M_Boy76
  • 16
  • 10
  • 8
34 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22820435
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!
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22820552
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
0
 

Author Comment

by:M_Boy76
ID: 22820657
I did excatly that but changed the delimiter to {CR}, and still no results
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22820701
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,
0
 

Author Comment

by:M_Boy76
ID: 22820779
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
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22820824
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
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22820845
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
0
 

Author Comment

by:M_Boy76
ID: 22820917
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.....
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22821261
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
0
 

Author Comment

by:M_Boy76
ID: 22821344
how can i attach a dtsx package here?
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22821362
rename to TXT and check the "Attach File"
0
 

Author Comment

by:M_Boy76
ID: 22821460
renamed it but still no able to attach, this is it on notepad
test.txt
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22821534
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
0
 

Author Comment

by:M_Boy76
ID: 22821900
here it is....
testscreenshot.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22822176
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.
 
0
 

Author Comment

by:M_Boy76
ID: 22822442
Could this be the reason why there is also a line break within the output flatflie?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22822461
Probably.
0
 

Author Comment

by:M_Boy76
ID: 22822709
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?
0
 

Author Comment

by:M_Boy76
ID: 22822717
I mean:

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

Expert Comment

by:HoggZilla
ID: 22822778
Since it is going to a file, you can use a zero length char like this: ''
0
 

Author Comment

by:M_Boy76
ID: 22822853
Got the following error:

Error 0xc00470d4:
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22822905
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.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22822926
why you dont store the SQL query inside database as a view or stored procedure?
regards
0
 

Author Comment

by:M_Boy76
ID: 22823114
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.
0
 

Author Comment

by:M_Boy76
ID: 22823180
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.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22823230
"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!
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22823292
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.
0
 

Author Comment

by:M_Boy76
ID: 22823329
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
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22842232
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
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22904045
improvements?!
0
 

Author Comment

by:M_Boy76
ID: 22904414
nah , not at all ....a graphical explanation on where to place each task might be handy...
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22904427
refresh our memory and tell us whta you alredy done...
regards!
0
 

Author Comment

by:M_Boy76
ID: 22904440
I have gotton the part of creating the file but it doesnt appear incremental, as earlier explained, o just gt lost
0
 

Accepted Solution

by:
M_Boy76 earned 0 total points
ID: 23546494
Hiya

Found what the problem was, I had to remove the "USE database" at the beginning of the query....
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question