Modify Input data

Question

Post reply

Modify Input data

mmonline

Old Hand

Points: 359
More actions
April 13, 2009 at 5:55 pm

#202064

Hi,
I have a text which has following data.
Bank Name / Holding Co NameNatl RankBank IDBank LocationCharterConsol AssetsDomestic AssetsPct DomesticAssetsPct CumulativeAssetsDomestic BranchesForeign BranchesIBFPct ForeignOwned
PACIFIC WESTERN NB/EGGEMEYER ADVISORY CORP237494261SANTA MONICA, CANAT1,8631,86310090440N0.00
PACIFIC WESTERN NB/WJR CORP237494261SANTA MONICA, CANAT........
CHINATRUST BK USA/CHINA TR HC238996260TORRANCE, CASNM1,8511,85110090170N100.00
GREAT SOUTHERN BK/GREAT SOUTHERN BANCORP INC239572374REEDS SPRING, MOSNM1,8431,84310090290N0.00
In this data, first row contains column names and the other rows have column values.
I want to replace "." in second row with " "(blank) in destination table. I want to know how to do this in SSIS.
destination table structure:
CREATE TABLE [OLE DB Destination] (
[Bank Name Holding Co Name] varchar(57),
[Natl Rank] smallint,
[Bank ID] int,
[Bank Location] varchar(23),
[Charter] varchar(3),
[Consol Assets] real,
[Domestic Assets] real,
[Pct DomesticAssets] smallint,
[Pct CumulativeAssets] smallint,
[Domestic Branches] real,
[Foreign Branches] smallint,
[IBF] varchar(1),
[Pct ForeignOwned] real
)
Please let me know if any one knows..thank u

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

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

Aparna-1 SSCrazy Points: 2339 More actions · Answer 1

Is it a variable length file and what is your column delimiter?

mmonline Old Hand Points: 359 More actions · Answer 2

tab is my column delimiter.

basically, I want to extract data from text file and import it into a database. while doing this I am getting errors because data is not matching.

Aparna-1 SSCrazy Points: 2339 More actions · Answer 3

While reading the data from the file read all the columns as string and then use the data flow transformation tools like "Data Conversion" to convert to int/float .

Basically its is a three step process

1. Read the contents from the file (While reading from the file make all the columns as string look at the Advance option in file connection manager)

2. Convert the string to the required data type (ofcourse you can specify your conditions here)

3. Load the data into the destination table.

For step 2 look at the data flow transformation tasks like script component, data conversion etc