Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1215
  • Last Modified:

SSIS to FlatFiles

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
M_Boy76
Asked:
M_Boy76
  • 16
  • 10
  • 8
1 Solution
 
PedroCGDCommented:
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
 
HoggZillaCommented:
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
 
M_Boy76Author Commented:
I did excatly that but changed the delimiter to {CR}, and still no results
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
HoggZillaCommented:
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
 
M_Boy76Author Commented:
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
 
HoggZillaCommented:
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
 
PedroCGDCommented:
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
 
M_Boy76Author Commented:
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
 
PedroCGDCommented:
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
 
M_Boy76Author Commented:
how can i attach a dtsx package here?
0
 
PedroCGDCommented:
rename to TXT and check the "Attach File"
0
 
M_Boy76Author Commented:
renamed it but still no able to attach, this is it on notepad
test.txt
0
 
PedroCGDCommented:
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
 
M_Boy76Author Commented:
here it is....
testscreenshot.bmp
0
 
HoggZillaCommented:
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
 
M_Boy76Author Commented:
Could this be the reason why there is also a line break within the output flatflie?
0
 
HoggZillaCommented:
Probably.
0
 
M_Boy76Author Commented:
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
 
M_Boy76Author Commented:
I mean:

Null as COL15, what can i do to ensure that null does not appear as a value within the CSV file?
0
 
HoggZillaCommented:
Since it is going to a file, you can use a zero length char like this: ''
0
 
M_Boy76Author Commented:
Got the following error:

Error 0xc00470d4:
0
 
HoggZillaCommented:
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
 
PedroCGDCommented:
why you dont store the SQL query inside database as a view or stored procedure?
regards
0
 
M_Boy76Author Commented:
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
 
M_Boy76Author Commented:
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
 
PedroCGDCommented:
"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
 
HoggZillaCommented:
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
 
M_Boy76Author Commented:
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
 
PedroCGDCommented:
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
 
PedroCGDCommented:
improvements?!
0
 
M_Boy76Author Commented:
nah , not at all ....a graphical explanation on where to place each task might be handy...
0
 
PedroCGDCommented:
refresh our memory and tell us whta you alredy done...
regards!
0
 
M_Boy76Author Commented:
I have gotton the part of creating the file but it doesnt appear incremental, as earlier explained, o just gt lost
0
 
M_Boy76Author Commented:
Hiya

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now