November 10, 2010 at 11:16 am
Hi
Want to make SSIS package
it will take file from folder and insert that file's data in SQL server
every day in folder i receive different type of file like 1.txt, 2.csv, 4.xml
i just wnat to send data to sql and in that it has to create table like
table 1
table 2 i mean name of file as a table name so every file need to create table and is having different schema on that
can you help me how to achive this??????
November 10, 2010 at 12:18 pm
SSIS can't handle this to my knowledge.
What you're going to look to do is build a generic table with a VARCHAR(MAX) field in it, and use that as your original data dump location. Whatever the file is, just dump each row in wholesale into this table.
Now, you'll start a series of detection procedures you'll have to build yourself to detect the type of file, and then what to do with it. You're going to basically be creating a file importer from scratch. It's not easy and is going to take a lot of time.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 1:56 pm
shirishjani (11/10/2010)
HiWant to make SSIS package
it will take file from folder and insert that file's data in SQL server
every day in folder i receive different type of file like 1.txt, 2.csv, 4.xml
i just wnat to send data to sql and in that it has to create table like
table 1
table 2 i mean name of file as a table name so every file need to create table and is having different schema on that
can you help me how to achive this??????
I think this is possible to do, but the solution will vary in level of difficulty depending on if each of the different file types have different numbers of columns or different column names. If the column names and number of columns is static, then I think the solution becomes a lot easier.
You will need to take the filename that gets captured in a ForEach loop variable and use that in a create table and insert into table statements.
Take a look at this article for ideas:
http://www.rafael-salas.com/2009/05/ssis-foreach-loop-looping-through.html
November 10, 2010 at 2:00 pm
I agree with you it's possible, but the 4.xml statement was the one that got me. He's overloading the load with multiple types, which is gonna go ugly. 🙂
The biggest problem he'll have is that the metadata can't be updated at runtime, only design-time. Between that and the fact that he stated these will come in with different schema led me away from even suggesting a script to detect file type structures to use multiple data flows. He's going to need a complete alternative to SSIS for this as far as I know. It just doesn't want to do it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 10, 2010 at 2:20 pm
Craig Farrell (11/10/2010)
@tmitchelar,I agree with you it's possible, but the 4.xml statement was the one that got me. He's overloading the load with multiple types, which is gonna go ugly. 🙂
I equally agree with you! Although possible, he's probably looking at lots of dev time even if he's proficient in VB. I wouldn't want to be in his shoes!
November 11, 2010 at 10:34 am
Suppose i have file name and table the can i achieve that
i mean i have table there now
Supposse i have file 123.txt i need to insert that in to table which is ready named 123
i have another file 456.csv to table 456
can i achive that?
November 11, 2010 at 10:40 am
My answer will provide roughly as much detail as your question.
Yes.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 11, 2010 at 1:01 pm
If from folder i take file one bye one
suppose
1 is jann.txt then i have that table for that schema is ready so just want to insert in table named jann
2 sss.csv then insert in to table which is already there name sss
Like that
I mean by looking on file name i want to insert then in specific table(which are already there in database) dynamically
Can i achieve that?
November 11, 2010 at 1:07 pm
shirishjani (11/11/2010)
If from folder i take file one bye onesuppose
1 is jann.txt then i have that table for that schema is ready so just want to insert in table named jann
2 sss.csv then insert in to table which is already there name sss
Like that
I mean by looking on file name i want to insert then in specific table(which are already there in database) dynamically
Can i achieve that?
Do columns change or are they always the same?
November 11, 2010 at 1:12 pm
different file has different column and different table destination
November 11, 2010 at 1:14 pm
Do you know what the file definition will be for each table ahead of time?
November 11, 2010 at 1:24 pm
yes like my table are ready
just reading file name and transfer data to particular table
like if file name is sss.txt transfer to table name "sss"(this table is already exist in Db)
and go to read next file read name and again look for particular table belongs to that
November 11, 2010 at 2:11 pm
I would loop through your files and use presedence constraints to determine which table to write to.
November 11, 2010 at 2:21 pm
tmitchelar (11/11/2010)
I would loop through your files and use presedence constraints to determine which table to write to.
If I understood the situation correctly, this is the approach I would take. Have a predefined data flow for each possibility and use expressions in the precedence constraints to determine which data flow to execute.
Looks like we have 2 votes for the same solution.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply