Okay, I have a table into which I import a comma delimited text file each day. Right now this whole process is performed manually by end users in an Access front end. I want to automate the process using a Job to import the data and disperse it to the work flow tables without the end users having to do anything.
Although I still have to work out how to teach SQL to find the exact file, I have someone working with me on that one.
But I am stuck on the import/inserting of the data. I have spent hours looking through Bulk Insert and Tasks and other options. I cannot find anyway to import this data directly into the table, every thing I find has a CREATE TABLE line to start the whole process. The import table itself is emptied each day after the import, but since I was working from the front end initially, and (based on experience, not so much knowledge) Access does not seem to like temporary table. The queries to manipulate the data and disperse the data to other tables, so the table exists.
So far, I am kind of stuck. I would rather not have to rebuild every one of the steps in my Job to accommodate a temp table
Should my work to automate this process change the process in its thinking to using a temp table, would that save me a lot of frustration?
Also, I set up a task using the Task Editor, but I have no idea where it went. I can't find the Task anywhere. It is a one-time kind of thing? If not, where can I find the task and can the file to import name contain wild cards? The report drops every day with a static first part of the name, underscore, date in MMDDYY, and time in HHMMSS. Since the time is unnecessary, could the file name be something like: \networkLocation\Folder\StaticFileName_(GetDate())%.txt? There would probably be some format statements in there I am guessing to make sure SQL knew it should look for today's date in the MMDDYY format.
Right-click database in SSMS, Tasks..., Import Data. Use the wizard to step through which fields in your CSV file you want to import. At the end of the wizard you'll be asked to save this SSIS package (the wizard is creating an SSIS package for you behind the scenes). Save it. Once you do you'll be able to schedule this package to run on a set schedule and not have to manually intervene again.
answered Oct 12, 2009 at 08:34 PM
Hi - Jorge is absolutely right that the Import/Export Wizard is the best place to create a starting point SSIS package. You will generally find however that you need to make some edits on that package (in BIDS - Business Intelligence Development Studio) to make it work repeatedly.
For example, if you don't want the task to repeatedly try and CREATE TABLE each day (if I understood your question correctly) then you may well need to make changes to the package to reflect this. Also, if you want the package to pick up different source flat-files each day with different filenames, this would also need to be done by changing the package.
Additionally, if you want to disperse the data to different locations (rather than just a single table), this can also be done by editing the SSIS package. Finally you can add in features such as "Email me when something goes wrong!" and so on which can be very helpful..
answered Oct 13, 2009 at 03:03 AM
My answer to this is to start with SSIS and export the resulting code to VB.NET. (Yes there is some massaging going on) This allows me to use a configuration file so that the VB.NET can modify the SSIS parameters dynamically. I then configure the program on the user's desktop which allows each user to do the import from their desktop by using the VB.NET program.
answered Oct 16, 2009 at 02:31 PM