| Author |
Topic |
|
benking9987
Posting Yak Master
124 Posts |
 Posted - 2010-02-18 : 19:34:25
|
| I have a BULK INSERT statement that works perfectly on one file when I am able to specify that file name.HOWEVER, I have a directory that will periodically have anywhere from 1 - 10 CSV files in it that ALL need to be bulk imported. As you can imagine, all files will have differing file names, but should all start with the same 10 or so characters.Does anyone know how I can import all the files in the folder, one at a time, without specifying each file name?Once a file has been BULK INSERTED it would also be helpful to delete the file (or move it elsewhere) so the destination directory is always cleared out....Any help is greatly appreciated. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-18 : 21:37:15
|
| You need SSIS Package Where you would use FOR EACH LOOP CONTAINER to loop the folder and FILE SYSTEM TASK to move processed files. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-19 : 12:04:06
|
| Whoa, I think I'm way out of my league here. I was hoping for some special T-SQL suggestions to make this easier but looks like T-SQL won't do the job. I'm unfamiliar with SSIS package. Is this an add-on to SQL Server 2005? Can someone explain to me more about the FILE SYSTEM TASK?Thanks all for your replies:@X002548: Can you explain to me xp_cmdsell? Is this anther option? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-19 : 12:15:14
|
| " I was hoping for some special T-SQL suggestions to make this easier but looks like T-SQL won't do the job."OK avoiding SSIS which you are not familiar with, and building on your existing Bulk Insert code:1) TSQL can give you a directory listing - from that you could extract the Filenames, and process them (one by one) with your existing BULK INSERT statement.2) You could have a batch file that works out what files are there, and then uses OSQL or SLQCMD (a command line tool for running SQL statements) execute your existing BULK INSERT code (substituting the filename)(1) may have permissions issues if your server is locked down tight (permissions can be relaxed, of course, but in a corporate environment that may not be allowed)(2) will require some messing around with BATch files - which you may not be familiar with |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-19 : 12:21:15
|
| Thank you Kristen. Common theme...."with which you are not familiar". I am actually pretty new to SQL Server. I understand your logic, but I'm unfamiliar with OSQL and SLQCMD. I'll google and see what I can find.Going with what you have here, do I have the option to delete or move the files once I'm done with them? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-19 : 12:34:00
|
| "I'm unfamiliar with OSQL and SLQCMD."OSQL comes with SQL Server. (SQLCMD does too, but may not be the right tool, on reflection)From command prompt your type something likeECHO OFFFOR %%f IN (XXXX*.CSV) DO osql -E -Q "BULK INSERT '%%f')"Needs to be saved in MyFile.BAT, and then just type MyFile.BAT at the prompt.Here's a simple example:ECHO OFFFOR %%f IN (XXXX*.CSV) DO ECHO '%%f'which will just list the names of the CSV files (starting with "XXXX") which you can use as a check that you are on the right track."Going with what you have here, do I have the option to delete or move the files once I'm done with them?"You can delete them in the BATCH file after processing, or the same route that gets you the Directory Listing directly from SQL can run any other command, so could do the DELETE.I'd be a bit nervous about deleting them - in case something went wrong and you needed to repeat. I'd be move inclined to MOVE them to a "Done" folder, and then have something clear that out periodically. |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-19 : 15:02:34
|
| Thank you for the explanation Kristen. I will try this. I have my network admin looking into SSIS installation (apparently he didn't install that pack with the version of SQL have). When I can get that and toy around with it, i think will be able to find which solution will work the best for what I need it. Fortunately, our environment is pretty small so i should pretty much be able to do whatever is needful.Thanks again for your help. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-22 : 18:02:02
|
| Thanks X002548, but I'm thinking SSIS is going to be the much easier and more robust tool for developing this particular need. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-23 : 10:04:07
|
quote:
Originally posted by benking9987
Thanks X002548, but I'm thinking SSIS is going to be the much easier and more robust tool for developing this particular need.
then try the steps outlined in the link------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-23 : 10:21:32
|
| I plan to do so visakh16....We are just having a few issues getting the Business Intelligence component of SQL installed properly. We went from a SQL Express installation to a full version of SQL 2005 Standard a while back. Apparently upon upgrading, there are some problems with installing certain components after the fact if you didn't install them to begin with. My network admin is working on that today. Now that I'm a little more familiarized with SSIS I am excited to try out your suggestion. I will let you know how it goes. |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-25 : 18:26:36
|
| visakh16 you are a genius. Thank you. This worked perfectly.Now I need to explore the FILE SYSTEM TASK to move the files. Does anyone know anything about that? Or should I start a new topic so as to properly assign credit for this topic?Thanks again. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-26 : 08:52:14
|
| http://rafael-salas.blogspot.com/2007/03/ssis-file-system-task-move-and-rename.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-02-26 : 19:00:35
|
| visakh16, thank you for the reference. This is definitely pointing me in the right direction, but I am having a hard time figuring out what he has done specifically with the variables. I am able to find quite a bit of help where people demonstrate how a FileTask works, but I'm not sure how to get it to interact with the For Each Fle loop container. Any suggestions on something to look at? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-26 : 23:33:43
|
| so are you trying to process through each and every file in folder and move them using file system task?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-03-01 : 09:30:00
|
| Yes. The process flow should be: 1) Import file A, 1a) Move file A, 2) Import File B, 2a) Move File B.Alternatively it should work the same if it were laid out like this:1) Import File A2) Import File B.......)Move File A...)Move File BDepending on which of these two methods is best I would assume determines whether I have 1 For Each Loop Container containing both tasks, or whether I have two For Each Loop Containers.I prefer having 1 For Each Loop Container and that is what I'm trying to work with, but I think I'm having trouble with the variables.Thanks for your help. |
|
|
|
benking9987
Posting Yak Master
124 Posts |
Posted - 2010-03-01 : 09:50:55
|
| I just tried a couple other arrangements with the variables and it seems to be working just fine now... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-01 : 10:36:20
|
| great------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
BULK INSERT