<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 - General
»
SSIS import multiple csv files by name
14 posts, Page 1 of 2
1
2
»»
SSIS import multiple csv files by name
Rate Topic
Display Mode
Topic Options
Author
Message
adb2303
adb2303
Posted Friday, February 08, 2013 1:28 AM
SSChasing Mays
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:13 AM
Points: 653,
Visits: 1,850
Hi,
I have an ever increasing number of files in a single directory (will probably end up with hundreds). There's 5 different types of files, denoted by their file name. For example, I may have (for each file typ):
ABC
ZZZZ
123.CSV
D
ZZZZ
872.CSV
123999_
ZZZZ
.CSV
87182
XXXX
ASDFA.CSV
DX11
XXXX
A1.CSV
D21ZZ
XXXX
.CSV
All files-of-type have identical structures/column headers, etc. That is, all ZZZZ files are identical, all XXX files are identical, etc.
I need to load all of these files, by type, into SQL Server. I have created a table for each of the different file types. I can successfully process a single file (explicitly specifying its name) into its table.
So, to load all csv files, I guess I need a ForEachLoop container in SSIS, and some script to split the file name to pick out the type of file it is? My SSIS skills are pretty dire, and I don't really know how best to do this.
Any guidance/solution would be gratefully received.
Thanks,
Andrew
Post #1417500
Want a cool Sig
Want a cool Sig
Posted Friday, February 08, 2013 7:33 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221,
Visits: 452
Not sure if it'll work but it's something to try...
Try the Conditional Split Task.
Capture the file name and parse out the file type in the loop and store in variables.
Then set the conditions of the splits to the type of file.
set the destination for each condition path.
Probably need to set the DelayValidation to True and Mapping might be an issue.
Probably have to map each file type one at a time first and live with the red dot for a bit.
I think I've done something like this before with the Condition split but it's been 6 years....
---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post Attachments
SSISConditionalSplit.JPG
(
2 views,
37.17 KB
)
Post #1417702
opc.three
opc.three
Posted Saturday, February 09, 2013 8:08 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 6,698,
Visits: 11,726
- removed -
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1418023
opc.three
opc.three
Posted Sunday, February 10, 2013 7:58 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 6,698,
Visits: 11,726
I re-read the original post this morning and realized I had misread an important requirement so I removed my last post which was way off the mark.
I do not think a Conditional Split is necessary here. Since all files have the same structure you can have a ForEach Loop Container (FELC) with a single Data Flow Task. The Data Flow Task will contain a Flat File Source and an OLE DB Destination. The Flat File Source will refer to a Flat File Connection Manager that will build it's ConnectionString using the Variable the FELC uses to store the file name. The
Data access mode
of the OLE DB Destination will be
Table name or view name variable
. The Variable it points to will have
EvaluateAsExpression
set to True and will use the file name Variable in its Expression to determine the table name.
e.g. this expression says, if the filename contains ZZZ then return TableZZZ, else if it contains XXX return TableXXX, else return TableDefault. You can expand this as necessary just use parentheses liberally to ensure it remains organized
FINDSTRING( @[User::FileNameVariable], "ZZZ", 1 ) > 0 ? "TableZZZ" : (FINDSTRING( @[User::FileNameVariable], "XXX", 1 ) > 0 ? "TableXXX" : "TableDefault")
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1418109
Jeff Moden
Jeff Moden
Posted Sunday, February 10, 2013 2:27 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902,
Visits: 26,783
adb2303 (2/8/2013)
Hi,
I have an ever increasing number of files in a single directory (will probably end up with hundreds). There's 5 different types of files, denoted by their file name. For example, I may have (for each file typ):
ABC
ZZZZ
123.CSV
D
ZZZZ
872.CSV
123999_
ZZZZ
.CSV
87182
XXXX
ASDFA.CSV
DX11
XXXX
A1.CSV
D21ZZ
XXXX
.CSV
How do you intend to keep track of which files were loaded and which were not?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1418141
adb2303
adb2303
Posted Sunday, February 10, 2013 2:43 PM
SSChasing Mays
Group: General Forum Members
Last Login: Friday, May 17, 2013 4:13 AM
Points: 653,
Visits: 1,850
The users will be responsible for clearing out the directory these files are stored in. My instructions are to load whatever's in that directory every time the solution runs, even if the same file gets loaded over and over again. I have a process in place within the SSIS package to archive/de-duplicate data.
Post #1418143
Jeff Moden
Jeff Moden
Posted Sunday, February 10, 2013 4:49 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902,
Visits: 26,783
adb2303 (2/10/2013)
My instructions are to load whatever's in that directory every time the solution runs, even if the same file gets loaded over and over again.
Wow. Whoever wrote those instructions has a serious hooie problem. If the database is designed correctly, it won't let you make such a mistake as what they're askig for.
Since you don't have to actually move files, rename them, or anything else that requires any real power, why not simplify all of this just by using BULK INSERT right straight from some scheduled T-SQL stored procedures? After all, the data is just some consitently formatted CSV files, isn't it?
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1418151
opc.three
opc.three
Posted Sunday, February 10, 2013 5:22 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 6,698,
Visits: 11,726
SSIS OLE DB Destinations are using the same API that BULK INSERT would. The only difference here is that SSIS can access the file system natively whereas xp_cmdshell would have to be enabled if wanting to do the same using T-SQL, a clear disadvantage imho.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1418154
Jeff Moden
Jeff Moden
Posted Sunday, February 10, 2013 6:11 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902,
Visits: 26,783
opc.three (2/10/2013)
SSIS OLE DB Destinations are using the same API that BULK INSERT would. The only difference here is that SSIS can access the file system natively whereas xp_cmdshell would have to be enabled if wanting to do the same using T-SQL, a clear disadvantage imho.
Absolutely not true, Orlando. There is absolutely no need of xp_CmdShell for this given "if it's there, load it" circumstance.
--Jeff Moden
"
RBAR
is pronounced "ree-bar" and is a "Modenism" for "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1418158
opc.three
opc.three
Posted Sunday, February 10, 2013 6:20 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 6,698,
Visits: 11,726
My understanding is the file names, while showing similar patterns, change from day to day.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1418164
« Prev Topic
|
Next Topic »
14 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
