Nightly Data Structure Refresh

We are receiving a reporting database from a vendor nightly. We want to
upload this data and structures to SQL Server nightly. We want it to be as
flexible as possible - if the vendor adds/deletes a table/column, the import
job should handle that.
Apparently, the Import/Export wizard in SQL Server and the Upsizing Wizard
in Access both require you to specify what tables to import. This would
prohibit the job from creating new tables/columns on the fly. Any
suggestions for a solution to this problem?
Thanks for the help...Backup and restore?
Transfer Objects Task ?
Darren Green
http://www.sqldts.com
http://www.sqlis.com
"Steve R" <SteveR@.discussions.microsoft.com> wrote in message
news:116327AF-3861-4406-845A-020093FAF674@.microsoft.com...
> We are receiving a reporting database from a vendor nightly. We want to
> upload this data and structures to SQL Server nightly. We want it to be
> as
> flexible as possible - if the vendor adds/deletes a table/column, the
> import
> job should handle that.
> Apparently, the Import/Export wizard in SQL Server and the Upsizing Wizard
> in Access both require you to specify what tables to import. This would
> prohibit the job from creating new tables/columns on the fly. Any
> suggestions for a solution to this problem?
> Thanks for the help...
>|||Couple of questions:
1. Are you trying to append this information to an existing reporting
database, or are you simply discarding a database daily and replacing
it with this new reporting db?
2. What format is the reporting db in (Access, SQL Server)?
3. How much control do you have over the vendor deliverable? Do you
have administrative rights on the box from whence the data comes, or is
it some form of EDI?
Stu|||1. We want to discard the database daily and replace it with the new db.
2. The vendor delivers an Access database, which we want to upload/import
into SQL Server.
3. We do not have control over the vendor's deliverable. As far as we are
concerned, it is only a file to be FTP'ed to us nightly. We will process
from there.
"Stu" wrote:

> Couple of questions:
> 1. Are you trying to append this information to an existing reporting
> database, or are you simply discarding a database daily and replacing
> it with this new reporting db?
> 2. What format is the reporting db in (Access, SQL Server)?
> 3. How much control do you have over the vendor deliverable? Do you
> have administrative rights on the box from whence the data comes, or is
> it some form of EDI?
> Stu
>|||We are starting with the Access database and I am not sure how a backup and
restore would work with this. The Transfer Objects Task only works from one
SQL Server to another, correct?
"Darren Green" wrote:

> Backup and restore?
> Transfer Objects Task ?
> --
> Darren Green
> http://www.sqldts.com
> http://www.sqlis.com
> "Steve R" <SteveR@.discussions.microsoft.com> wrote in message
> news:116327AF-3861-4406-845A-020093FAF674@.microsoft.com...
>
>|||I needed to handle something similar a while back.
Here are the steps that I did:
1) Created a DTS and this import looks today.
2) Exported the DTS to VB.
3) Cleaned up the DTS for to add a new task would be calling a procedure.
4) For each table in Access get the field name.
5) Call the procedure to add each field to the DTS (in VB code)
I know this is a breif explaination of what I did, but if you want more info
let me know.
Rick
"Steve R" <SteveR@.discussions.microsoft.com> wrote in message
news:180C1DD5-7261-474E-B560-723CFAF56010@.microsoft.com...
> 1. We want to discard the database daily and replace it with the new db.
> 2. The vendor delivers an Access database, which we want to upload/import
> into SQL Server.
> 3. We do not have control over the vendor's deliverable. As far as we are
> concerned, it is only a file to be FTP'ed to us nightly. We will process
> from there.
>
> "Stu" wrote:
>|||Is the requirement to merge this with an existing DB or just to load
the vendor's DB onto your server. If it's the latter then just
BACKUP/RESTORE or detach/attach.
If you actually want to merge client schema changes into your own DB
while keeping your existing data then you face quite a challenge.
Suppose the data you already have in your tables violates the
constraints, datatypes or other metadata changes that the client has
made? Do you then want to delete invalid data from your database?
It's certainly possible to automate or semi-automate simple changes
like adding or removing columns. For that you'll probably want a
package with more sophisticated metadata management features than DTS
has. Consider purchasing some third-party ETL tools for the job.
David Portas
SQL Server MVP
--|||I want to load the vendor's DB onto my server from Access to SQL Server. We
will completely replace the data and schema nightly.
Can you do a backup/restore or detach/attach from Access to SQL? if so, can
it be scheduled?
"David Portas" wrote:

> Is the requirement to merge this with an existing DB or just to load
> the vendor's DB onto your server. If it's the latter then just
> BACKUP/RESTORE or detach/attach.
> If you actually want to merge client schema changes into your own DB
> while keeping your existing data then you face quite a challenge.
> Suppose the data you already have in your tables violates the
> constraints, datatypes or other metadata changes that the client has
> made? Do you then want to delete invalid data from your database?
> It's certainly possible to automate or semi-automate simple changes
> like adding or removing columns. For that you'll probably want a
> package with more sophisticated metadata management features than DTS
> has. Consider purchasing some third-party ETL tools for the job.
> --
> David Portas
> SQL Server MVP
> --
>|||I want to load the vendor's DB onto my server from Access to SQL Server. We
will completely replace the data and schema nightly.
Can you do a backup/restore or detach/attach from Access to SQL? if so, can
it be scheduled?
"David Portas" wrote:

> Is the requirement to merge this with an existing DB or just to load
> the vendor's DB onto your server. If it's the latter then just
> BACKUP/RESTORE or detach/attach.
> If you actually want to merge client schema changes into your own DB
> while keeping your existing data then you face quite a challenge.
> Suppose the data you already have in your tables violates the
> constraints, datatypes or other metadata changes that the client has
> made? Do you then want to delete invalid data from your database?
> It's certainly possible to automate or semi-automate simple changes
> like adding or removing columns. For that you'll probably want a
> package with more sophisticated metadata management features than DTS
> has. Consider purchasing some third-party ETL tools for the job.
> --
> David Portas
> SQL Server MVP
> --
>|||I'm not as familiar with Access as I used to be, so take the following
ideas as conceptual only; I think that you can use VBScript to open up
an Access database, and cycle thruhg all of the table and view objects
in that database, generating the appropriate SQL creation statements
for those objects. You'll have to avoid crosstab queries, but I think
those are the only Access views that won't translate into SQL Server (i
could be wrong).
You'll then have to drop the database on SQL Server (if it exists), and
then run the newly created SQL script that you generated to create
those objects. You'll then need to copy data from your tables to your
target database. It can be done, but it'll be a helluva DTS package,
and could take a while to run each time. You could probably accomlish
all of it in a single ActionScript item.
It actually sounds like fun, but then I'm a g :)
Stu