| Author |
Topic |
|
dhumphries
Starting Member
18 Posts |
 Posted - 2008-12-29 : 16:45:07
|
| I have a project were I need to be able to export the results of a sql statement to a flat file. while this is simple enough the statements I am using largely do not produce a column for export so I can no do any linking. I would prefer not to create a mess of temp table so how can I get it there. Here is an example of the statementselect '1: GROSS', sum(PPRD_CHK_GROS_AM )from pay_check_ext a, pay_check bwhere a. int_ctrl_no = b.int_ctrl_noand gtn_run_no = 762This result and how it needs to be in the flat file is below.----------- ---------------1: GROSS 1972306.77How can I complete this in SSIS or SQL 2005 in general |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-29 : 17:16:52
|
| I apologize I should have been more specific. I know how to do this several ways one being through a bcp but I need this to be a SSIS so that the end user can run a job and get the outputed file to be loaded into another application. I have looked online all over the place and can not figure out for the life of me how to output the raw result to a file in SSIS. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-29 : 17:27:10
|
| Yes I have tried to build the package thinking I could simply run a sql command produce the output and stick it in a file. problem is I have no destination cloumns to output. becuase the select statement I am running does not realy result in any columns. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-29 : 17:30:41
|
| I thought of that but the destination still does not see any columns available. |
|
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-29 : 17:33:17
|
| I am trying to run this in the OLE DB Command under the data flow source. would there be a better transform to use that would allow the information to pass to the file destination? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 00:49:28
|
quote:
Originally posted by dhumphries
I am trying to run this in the OLE DB Command under the data flow source. would there be a better transform to use that would allow the information to pass to the file destination?
did you try clicking preview button on OLEDB source and see if it picked up column headers correctly. After that you need to go to column mapping tab and select the column by clicking checkbox for them to get included in pipeline metadata. |
|
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-30 : 08:56:37
|
| I have looked at the source and no columns are selected. The reason for that is if you look at the select query I am running the output I am looking for is a sum of a column not necessarily a particular column in a table. |
|
|
|
dhumphries
Starting Member
18 Posts |
Posted - 2008-12-30 : 09:08:46
|
| I thought this might be a better disription of what I am trying to do.I have a series of select statements. When you enter those in the qury window of sql Server you can select output to file. This will run all the statements and write the output to the file rather then the screen. What I want to due is simply create a job that will do the exact same thing. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 11:45:28
|
quote:
Originally posted by dhumphries
I thought this might be a better disription of what I am trying to do.I have a series of select statements. When you enter those in the qury window of sql Server you can select output to file. This will run all the statements and write the output to the file rather then the screen. What I want to due is simply create a job that will do the exact same thing.
as you said earlier you can simply do this in ssis. but i just cant understand why this didnt work for you.b/w were you trying to execute all select statements in the same oledb source? also are all there resultsets similar? |
|
|
|
|
Output of SQL Statement to Text File