upload using flat file source

Question

upload using flat file source

harri.reddy

SSCommitted

Points: 1957
More actions
October 25, 2012 at 9:12 am

#266117

my file is like this
TH*4.2*857463*01**20091015*1045*P**~~IS*7564*ORACLE~
i have two table
table : TH
ID TH1 TH2 TH3 TH4 TH5 TH6 TH7 TH8 TH9
TH 4.2 857463 01 * 2009105 1045 p * ~~
TABLE : IS
ID IS01 IS02
IS 7564 oracle
now in my flat file connection manager i am using column delimeter as *,but then i cannot store values in
TH04 and th08
it just skip *,
how do i store value i shown here

Viewing 12 posts - 1 through 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

harri.reddy (10/25/2012)
my file is like this
TH*4.2*857463*01**20091015*1045*P**~~IS*7564*ORACLE~
i have two table
table : TH
ID TH1 TH2 TH3 TH4 TH5 TH6 TH7 TH8 TH9
TH 4.2 857463 01 * 2009105 1045 p * ~~
TABLE : IS
ID IS01 IS02
IS 7564 oracle
now in my flat file connection manager i am using column delimeter as *,but then i cannot store values in
TH04 and th08
it just skip *,
how do i store value i shown here

I've read this through three times & still 99% clueless as to what you want, I'm afraid. I can't even understand enough to ask for additional information.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

This look like EDI file. If I'm correct, then you can forget about processing it with the regular Flat File Connection Manager. The EDI format is hierarchical and more similar to an XML-type file.

If you can use third-party components, check the commercial COZYROC EDI Source component. It transforms the EDI data into tabular format that you can then load into a table.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

harri.reddy SSCommitted Points: 1957 More actions · Answer 3

well my data its not hierarchical,

for example

TH*4.0*347*01**20120514*091509*P*:~,IS*8007487001*Health~

if you can see TH*4.0*347*01**20120514*091509*P*:~,

i need to store in 1 table,

IS*8007487001*Health into another table

by seperating field,

do you think its EDI,if so how can i get that component ,please help me

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

The EDI Source component is part of the SSIS+ library. You can download the latest version from here. There are separate installers for 32bit and 64bit systems. When you test and develop from Visual Studio (BIDS), no license key is required.

You have to implement configuration file for your format. Ask whoever is providing this file to you to provide specification file for it. We have posted sample 852 configuration and input file that you can use as a reference to create your own configuration.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

harri.reddy SSCommitted Points: 1957 More actions · Answer 5

i am getting all this files from client ,so i cant have configuration

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

harri.reddy (10/25/2012)
i am getting all this files from client ,so i cant have configuration

They have to provide spec file for it. Otherwise it will be impossible or very hard to setup proper process for it.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

harri.reddy SSCommitted Points: 1957 More actions · Answer 7

ok,but i am getting multiple file in a folder,how can i pass file in edi source so that it can pick up one by one

COZYROC One Orange Chip Points: 28499 More actions · Answer 8

harri.reddy (10/25/2012)
ok,but i am getting multiple file in a folder,how can i pass file in edi source so that it can pick up one by one

You can setup the input file to be in SSIS variable in the component. Then you can setup expression to dynamically setup the variable based on current iterated file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

harri.reddy SSCommitted Points: 1957 More actions · Answer 9

ok,but we have to supply two files xml and .dat

will xml will be same for all files or it will be different,i am getting files from different companies.

if it is different, how can iset variable in for each loop container.

COZYROC One Orange Chip Points: 28499 More actions · Answer 10

harri.reddy (10/25/2012)
ok,but we have to supply two files xml and .dat
will xml will be same for all files or it will be different,i am getting files from different companies.
if it is different, how can iset variable in for each loop container.

If you have different EDI configuration, then you have to setup separate data flow for each individual configuration type.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

kishan.parmar Newbie Points: 9 More actions · Answer 11

HI i have same issue , i have EDI file but while getting file in dataflow first i use source assistance and row dalimiter is ~ and column delimiter is *
but my table is created like
heder ISA 00 00
Data 123 2 3
so error is i can not use same column name twice 00
but it should not be column..
what can i do?