[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 51
  • Last Modified:

Data from SQL table to text file

I need to output data from  SQL table to a text file with permanent header.
 sample output below.

header--stays the same always (in tableA)

sample  header output

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
                                                 
Sample details  line - (in table B)

Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

The header and details do not match.  The header does not change.  The details changes
every day.


Inputs appreciated
0
Nana Hemaa
Asked:
Nana Hemaa
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>header--stays the same always (in tableA)
In SSIS, go to the Connection Manager, double-click on whichever connection represents this textfile, and eyeballeth thy 'Header Rows to skip' listbox.  You can add a 1 here for SSIS to skip the header row, and process all other rows.

>The details changes every day.
If you mean the data changes, but the file format is the same, no problem.

If you mean the columns will change, this is going to be a show-stopper, as SSIS requires a 'contract' with a data source in order to consume it, meaning that the file spec has to be well defined and does not change.

Normally the only alternative is to import the entire row as a single column, then write T-SQL to figure out how to parse each column based on 'The
0
 
Surendra NathCommented:
you can use the BCP command to accomplish this task.

bcp "
select 'ABCDEFPASS','2345uuNA','ABCDEFG','ABCGFRT','F','ABC','TESTING'
UNION ALL
select * from <your Table>" queryout '<dir path>' -c -T

Open in new window

0
 
Nana HemaaAuthor Commented:
The data changes but the file format is the same--thx

>The details changes every day.
If you mean the data changes, but the file format is the same, no problem.
0
Technology Partners: 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!

 
Nana HemaaAuthor Commented:
can you pls. ellaborate the bcp-- at command prompt or via SQL management?
0
 
Nana HemaaAuthor Commented:
header and details do not have same number of columns for the UNION
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Abonsu - Let us know if you have an exact question in mind.  

'Inputs appreciated' is going to solicit every angle under the sun, whether you are looking for it or not.
0
 
Nana HemaaAuthor Commented:
need to know  how to get this accomplished via SSIS
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>need to know  how to get this accomplished via SSIS
In simple terms (can't speak for the BCP solution, as I don't have those skills)

(1)  Open Visual Studio, and create a new SSIS Solution
(2)  Create a connection to your text file, making sure you enter a 1 in the  'Header Rows to skip' listbox so it knows to skip the header row.  Then set all of the column data types.
(3)  Create another connection to wherever you want to pump this data.
(4)  Create a data flow task control, then double-click on it.
(5)  Create a source task to your flat file connection.
(6)  Create a destination task to wherever this file is going.
(7)  Save, then execute.

Or, you can always go into SSMS, then click on the database, then right-click:Tasks: Import Data..., and see if the wizard will complete the above for you.  If yes, make sure you save the resulting SSIS package.
0
 
Nana HemaaAuthor Commented:
Thanks Jim..I am confused at step 2.--Are you implying my destination flat file here

 I  save the header information from tableA(header table) to a text file called  header.txt since it is does not change.  


Header.txt
ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING    

I am not sure how to combine the header (header.txt) with the details in Table B to get the desired output in Text file   ---finaloutput.txt from above instructions

ABCDEFPASS          2345uuNA          ABCDEFG        ABCGFRT     F          ABC       TESTING      
Business 1        MANNTR    4567777              789999                80000
Business 2        FANNTR     6387999               789653               70000
Business 3        Bauctyr       4569877              369887               60000

I created a header flat file connection in ssis  pointing to Header.txt
I created an OLE DB Source to table B
0
 
ValentinoVBI ConsultantCommented:
The Flat File destination has a property called Overwrite. You can set that to False, that way you can use your header.txt as template.

Here's what the Control Flow would look like:

First use the File System Task to copy your template to a new location (or name).

Then use this new location/name in the Flat File destination and write your records to it.

Please note that the header line in the file should already end with a CRLF.
0
 
Nana HemaaAuthor Commented:
Thanks.  The output looks better but when I change the overwrite property to True...It appends the detail records and does not overwrite it each time the package runs.
0
 
ValentinoVBI ConsultantCommented:
but when I change the overwrite property to True

I think you meant False instead of True :)

It appends the detail records and does not overwrite it each time the package runs.

That's what the File System Task is for: first make a fresh copy of your template containing the header, then append records to that copy.  Through this method the Data Flow transform always starts with an "empty" file.
0

Featured Post

Industry Leaders: 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!

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now