Import Data From Access *.mdf Files to MS SQL SERVER 2000
-
Hi all,
I am having a few problems with our system the way its setup, but it cant be changed at the moment.
We have a data capture system that picks up data from our machines and saves the data as an yyyymmdd.mdb file, this changes daily depending on date it is saved, eg todays file is 20070830.mdb, which is causing a problem as i can not figure out how to do a daily import / refresh to bring all the data to a SQL database so that i can do crystal reporting on the data.
Regards
Steve
PS Sorry the subject should be *.mdb
-
Steve,
You can use DTS to build a package that will transfer data from a MDB (through the Jet engine) to a SQL Server table. Have a look at http://www.sqldts.com (when it's back up and running) for the basics on how to build a DTS package.
You can then schedule the package to run on a daily basis as a SQL Server Agent Job.
If you need more info, just shout.
-
Adrian,
Thanks for the reply, I have already done the DTS Package for importing the data, but this has to be done manually on a morning, this is due to the *.mdb files that change file name every day.
Yesterdays filename was 20070830.mdb, today's is 20070831.mdb
I need help writing the Sql statement in the DTS package that will look at todays date, then something like: Today() -1 (This equals yesterdays date) and create the filename from this.
Regards
Steve
-
Apologies, my misunderstanding...
You need an Execute SQL task with the statement:
SELECT CONVERT(CHAR(8), DATEADD(dd, -1, GETDATE()), 112) + '.mdb' AS filename
in it to build yesterday's filename. The DATEADD function is used to get yesterday's date and the CONVERT statement puts that date in to YYYYMMDD format.
You can then hit the Parameters button and assign the output to a Row Variable Parameter.
-
Adrian,
Thank you very much for that, I will go ahead and try this and let you know what problems i can come up with
Regards
STeve
-
Adrian,
That works fine for the filename, But in the next stage i need this filename to be inserted to the
\\Prodigy-2\tascomp ltd\Prodigy\Data\DownTime\03CDP_Low_Moor_Line_3\20070830.mdb
How do i declare that as a variable and put that into the line above, Excuse my naievity, as i have been using straight forward Db's upto now and not had to do this before
Regards
Steve
-
Steve,
Thinking back I remember tearing my hair out over a similar problem and decided that I could actually hate an inanimate object....
Anyway, back to the problem. If the path is fixed you can simply prepend it in the select statement, al la...
SELECT '\\Prodigy-2\tascomp ltd\Prodigy\Data\DownTime\03CDP_Low_Moor_Line_3\' + CONVERT(....
If the path isn't fixed, then it can get a bit tricky. Input parameters can be quite unforgiving but there are a couple of workarounds.
Fingers crossed for fixed! If not, shout back and I'll provide an example.
-
It is acually a directory on the server where all the data will be stored.
I am a little confused now, sorry about this.
I got the above file path from the connection 1 filename...
Should i scrap the execute Sql task and put the select statement in the filename for connection 1 or am i absolutely lost here
Regards
Steve
-
Adrian,
I now see that i still need the Execute SQL Task, I have done as you said and added the whole line because the paths will never change only the filename.
I am still stuck as to how to move the file path and name into the next task.
-
Steve,
You'll need the Execute SQL task as the first step in your package, this will build the filename and pass it to a Global Variable.
You then need to follow the SQL task with a Dynamic Properties task to assign the Global Variable value to the Connection. To do this:
1. Drag a Dynamic Properties task in to the Designer.
2. Click ADD.
3. Left-window: Expand Connections by clicking.
4. Left-window: Select MDB Connection by clicking.
5. Right-window: Double-click DATASOURCE.
6. Change Source to Global Variable.
7. Choose filename variable.
8. Click OK.
9. Click OK.
You then follow that with your transformation task.
In summary:
1. Start with the SQL task to build the filename.
2. Use Dynamic Properties task to assign filename to conn.
3. Transform the data.
4. Join each task with an on success workflow:
SQL --> Dynamic Properties --> MDB Connection of Transformation task
Hope this clears it up a bit for you.
-
Adrian,
Really sorry to keep troubling you like this, but way back at the beginning:
I missed the parameters button i get a parameters error " The SQL STATEMENT DOES NOT CONTAIN ANY PARAMETERS"
Not sure what this means...
Regards
Steve
-
Sorry Double Post
-
Hmm, not seen that one before...
Drop me an email to {email now removed} and I can send you an example package that I've created.
Just out of curiosity what platform, and build of SQL Server are you using?
-
I think it was because i was using the Access connection rather than the SQl connection
Steve
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply