August 30, 2007 at 2:32 am
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
August 30, 2007 at 9:21 am
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.
August 31, 2007 at 1:38 am
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
August 31, 2007 at 2:14 am
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.
August 31, 2007 at 2:18 am
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
August 31, 2007 at 3:04 am
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
August 31, 2007 at 3:24 am
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.
August 31, 2007 at 3:29 am
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
August 31, 2007 at 3:43 am
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.
August 31, 2007 at 3:45 am
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.
August 31, 2007 at 3:55 am
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
August 31, 2007 at 4:03 am
Sorry Double Post
August 31, 2007 at 4:12 am
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?
August 31, 2007 at 6:06 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy