Solved

SSIS to FlatFiles

Posted on 2008-10-28
34
1,166 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
Comment Utility
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
Comment Utility
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
Comment Utility
I did excatly that but changed the delimiter to {CR}, and still no results
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
how can i attach a dtsx package here?
0
 
LVL 22

Expert Comment

by:PedroCGD
Comment Utility
rename to TXT and check the "Attach File"
0
 

Author Comment

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

Expert Comment

by:PedroCGD
Comment Utility
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
Comment Utility
here it is....
testscreenshot.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
Comment Utility
Could this be the reason why there is also a line break within the output flatflie?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
Probably.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:M_Boy76
Comment Utility
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
Comment Utility
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
Comment Utility
Since it is going to a file, you can use a zero length char like this: ''
0
 

Author Comment

by:M_Boy76
Comment Utility
Got the following error:

Error 0xc00470d4:
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
Comment Utility
why you dont store the SQL query inside database as a view or stored procedure?
regards
0
 

Author Comment

by:M_Boy76
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
improvements?!
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:M_Boy76
Comment Utility
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
Comment Utility
Hiya

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now