Importing flat files

Question

Post reply

Importing flat files

sridharparshi

SSC Enthusiast

Points: 115
More actions
June 28, 2010 at 6:03 am

#141139

How can i import different flat files to different tables using single flat file source and single oledb destination
like can'nt we assign the dynamically flat file and based on the flat file which is assigned changes the destination.
my requirement is like when run the package i am initially ftp task is retriving the all the flat files from ftp server folder to my local folder then i have to run the foreach loop for processing the files which are imported to my local folder
so successfully retriving the files from server then i placed a foreach loop in that i placed dataflowtask
in dataflowtask i taken flatfile source and script task and oledb destination
so using filename variable i am assigning the file name to flat file source and then in script i am writing code like if filename variable contains text which is fixed for each type of file then i am assigning the table name to the variable
then in the oledb destination i am assiging the table name using tablename variable.
but unable to do this
i am getting errors
is this process is correct can you please tell me any one
i am bothering withis from 4 days no one is replying me

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

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

I guess no one replying to you due to the lack of details in your question.

Sample of existing script? Error details?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

Are all of these flat files and tables identical? Because the Data Flow contains meta data about the external sources and destinations. You cannot loop through files and tables that have different layouts by using the same Data Flow.

If they are identical, then you should be able to use variables and expressions to change the source and destination connection strings.

SSIS.COM SSCommitted Points: 1735 More actions · Answer 3

I am facing with the same problem...And I think we cannot do this dynamically...Can someone please confiem this..

sridharparshi SSC Enthusiast Points: 115 More actions · Answer 4

all these flat files are different from each other and destination tables are also different for every flat file

so now i got some idea like looping through the all the files in the directory i can have the filename so by this how can i assign the destination table dynamically is it possible by script task.

like after getting all the files into a variable in script looping through the each value in the variable

example

foreach filename in strfiles

if filename.contains("condo") then

some variable = "tableName"

and assigning this variable dybamically to the oledb destination

please replay on this

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

To be definite on the subject:

you can only use the dataflow (flat file source and OLE DB Destination) dynamically if (and only if):

* the flat files have the same identical structure

* the destination table is always the same (maybe you can use different tables if they have the same identical structure, but I'm not sure about that)

If this is not the case, you're stuck with scripting.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

sridharparshi (6/28/2010)
all these flat files are different from each other and destination tables are also different for every flat file
so now i got some idea like looping through the all the files in the directory i can have the filename so by this how can i assign the destination table dynamically is it possible by script task.
like after getting all the files into a variable in script looping through the each value in the variable
example
foreach filename in strfiles
if filename.contains("condo") then
some variable = "tableName"
and assigning this variable dybamically to the oledb destination
please replay on this

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task and includes support of completely dynamic data flows at runtime. You can process a list of flat-files with different layouts using only one data flow. Also setting up dynamic data flows doesn't require programming.

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

sridharparshi SSC Enthusiast Points: 115 More actions · Answer 7

this is very helpful and good one

but i should not use the third party tools in my work i have the restriction.

but i am very thankful to you for your great link 🙂

sridharparshi SSC Enthusiast Points: 115 More actions · Answer 8

all the files are .txt extension but different structure and they should be import to different tables.

sorry for my late response i out of the office for two days.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 9

OK then, a few more questions:

- Do the source files always have the same name?

- Are there always the same number of source files with unique formats?

- Are the destination tables always the same for each file format?

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 10

****Plese note the solution hereby is partly tested...It should work*****

Hi,

This is possible but will require certain measures are required which are to be considered:

1. The source file name is changing...One variable to store current path name

2. The destination SQL Table name is changing....One Variable to keep a track of DestnationTableName

Now using a ForEachLoop container in File Enumerator mode one may traverse the folder where the source files are placed. using the FileNameRetrieval attribute we will get the names of every file in the source folder in a package variable...

Now for the later part...we will us BIT(Bulk Insert Task)

Now create another variable where we would store names of all tables in the destination server database.

using the DestinationTableName attribute we will assign the new table name on each run of the for each loop

This should work...I'll test the later part...will let you know probably tomorrow...thanks anyways it's been time since i've encountered a chiller...:-P:-P:-P

Hope this helps

Raunak J

sridharparshi SSC Enthusiast Points: 115 More actions · Answer 11

Hi Raunak,

Thanks for you r replay

i got u r point but where should i place the flatfile source and how can i give the table name to variable

please respond to this.:-)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Ok... I've been watching this one for a bit and I'm curious... so let me ask a couple of questions, please.

1. It's my understanding that if a file name contains a particular "word" such as "condo", then that file should be imported into a particular table having to do with the word "condo".

Is THAT true?

2. If 1 above is true, does that mean that [font="Arial Black"]all [/font]files having the word "condo" in it all have the exact same file structure?

3. If 1 and 2 above are true, how many different "words" in file names (and, thus, file types) are we talking about?

4. If 1 and 2 above are true, does this have to be an SSIS solution or can we use a little T-SQL magic?

--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)