Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
osupratt
Posting Yak Master
238 Posts |
 Posted - 2009-10-26 : 20:43:48
|
| I am trying to plan on how a export/import package works. A little background is that I have an MS Access front-end of forms, queries, etc. linked to a Sql Server 2005 backend. The basic principle is that we do daily work tickets. There might be twenty work tickets that make up a Sales Order. On the Access front-end I have a basic form that I call an Export form that asks for the Sales Order number. The user will enter the Sales Order number when the job or Sales Order is complete. I have an update query that inserts the date time into a ClosedDate column. I'm wanting an SSIS or some export package that will export this data PER Sales Order automatically. Meaning, when the user types in Sales Order 12345 into the export form in Access; a file or something from the back-end is exported with just this data (sales order 12345 data). Also, once this file has been uploaded into my other system how can this be pushed back to my sql back-end to show that the file has been loaded into our other system?Could someone help me on this? I have the front-end query working correctly. Sorry, but I've never had to do this before. Thanks for any help you can provide. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2009-10-27 : 08:22:04
|
| The other system that we want to push data to is our Accounting system MAS90. They have a 'visual integrator' that runs import/export jobs. Are you saying to use this to have an ODBC connection job? If I did something like this I'm not sure of the logic for the MAS90 to 'know' when to import? If a clerk wants to 'close/export' records at any given point in time, then how will the MAS90 know..Ok, there are some records that just got closed I need to import them?Again, never had to do this before so it is ALL new to me. Thanks for any help. |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-10-27 : 10:11:09
|
| If your event to kick this off is based in access you may be better off looking at a file export task within access rather than muddy it by using sql server. You may need to dig in a little bit into vb script to pull that off. it's been a long time since I've done this type of thing in access. As for getting a confirmation back from the other system, that depends on what they're capable of. If they can connect to sql server, great, you can do an update to your table. if they can't you need to find a way to get a text file out from them with the information you need to update sql server. SSIS would be more appropriate for this piece or bcp. I don't think it makes sense for them to tell you each time 1 file is imported. maybe you could get an hourly/multiple time per day/or nightly file from them with confirmation of what was uploaded.Tara. Unfortunately a lot of IT shops still won't let systems go table to table even though they should for more reasons than I'm going to bother listing here. It's archaic but you're never going to get people to give up their text files.Mike"oh, that monkey is going to pay" |
|
|
|
osupratt
Posting Yak Master
238 Posts |
Posted - 2009-10-27 : 11:20:57
|
| We have SOC's issues that we can't deal with. But, MAS90 Visual Integrator imp/exp jobs can connect to ODBC datasource. i was thinking that it could pull from a View since i need to do some calculated fields, etc. if due to some SOC's thing that i'm not aware of then i will need to export from sql server to a file at a designated location and then have this data imported into MAS90 through Visual Integrator. not sure how i would do an Update from MAS90 back to sql server? really the only thing that i have going on in Access is an update query that shows records as closed (inserting closed date) and then locking down the appropriate records from any further editing. i really want to do everything out of sql server; now whether that is directly from a View or from a file that i export... i have no idea. |
|
|
|
|
|
|
|
|
Help with basic export/import package