Text file to sql server

Question

Text file to sql server

keywestfl9

SSCertifiable

Points: 5215
More actions
September 1, 2008 at 4:41 pm

#126146

CREATED DATE:08/20/2008
PLATT PLTFORM PLATNAME LOGONID
----- -------- -------- -------
GBSPFY xxx
RCH0Cb test TRestACI
RCHA4C test Etest hrdy
i have a text file in this format with first line with created date. i want to insert this file into sql table thru ssis as this file will be present in the mainframe FTP location.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Paul_J Hall of Fame Points: 3698 More actions · Answer 1

Please provide more details. What does the table look like? Same number of columns and datatypes as the file? etc... Also why do you need to use ssis? BCP can do this.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

Also, what are the delimiters between columns in the files? If fixed field, what is the starting position and length of each column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 3

i know the starting position of each column.

no delimiter for the text file.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

Then, you have several choices. A couple of them include...

1. BCP the file in using a format file.

2. Import the entire width of a file into a single column staging table and parse the columns using SubString.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)