I have to import 100+ tables from an ACCESS database into SQL Server
database for unspecified number of times.

The access database structure is exported from SQL Server DB, which
means they have the same table names and columns. The imported data
should be appended to existing SQL DB.

The access database contains data collected by different person. the
data will then be sent to and will be imported by SQL Server DBA. The
DTS should make the connection configuration flexible so that the same
DTS can be run on both test server and production server, and the Access
database location can be changed easily. I hope I can set in DTS Access
connection once (using global variable or .ini file).

The importing of tables has to follow a certain order so that the
constraints will be enforced during the import (i.e. importing primary
key table before tables with foreign keys.)

Can anyone tell me how to approach this task? Thank you.

Hello q_test,

OK because you have 100+ tables I would advise using the IMPORT/EXPORT wizard.
This will nicely generate a basic package with 100 * data pump tasks.


Your requirements.

To have the tasks go in order you will need to do the workflow yourself.
This is a one time job though

Making the connections configurable: After you have joined everything together
you add a Dynamic Properties task to the start of the package and make sure
it is the first thing that happens in the package. This will read from somewhere
and configure your data sources for you.

Is this an incremental load i.e. will the Access Db contain the SQL Server
data + new data or just new data?

The former will be more tricky to manage

Another thing the wizard will more than likely do is the DestinationObjectName
will be in the format of "Database.Owner.TableName" this seriously hampers
moving the package around. You can run a piece of code that will get rid
of the database name for you or you could go and use Disconnected Edit and
manually do it yourself from each of the 100 Data Pump tasks. Again this
should be a one time job.


Another "Non Wizard" way to do things would be to configure the Access DB
as a Linked Server. You can then generate the INSERT statements needed (incremental
insert). This could then be run inside a script called on the command line.
You could alter the order of the INSERT statements to suit your needs but
this way is relatively clean and requires less setup



Hopefully this has given you a few ideas of how to proceed.



Allan

Thank you for your response. Creating 100 data pump tasks is not
feasible and linked server is not an option for me either. The data in
Access DB is always new data (not incremental).

I have come up with the following code. In the DTS, there are two
connections (one for Access, one for SQL Server) and one task (importing
one table). I add an ActiveX Script task with the following code.

In short, it tries to change the property in data pump task and exectute
the task repeatly in the order of predefined table list (for now, only 7
tables). This way, I don't have to create 100 tasks.

However, this code doesn't work. It seems that I need to add
transformation columns one by one, which is not feasible.

If anyone can follow my code and make it work, I will be grateful.
Thanks a million.
================================
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
option explicit

Function Main()

Dim oPKG
Dim oTask
Dim table_list_order
Dim t_name '++Table Name
Dim first_table '++ first table in the task1


'++ The complete list of tables to be imported, all lower case
table_list_order =
"location,coordinate,station_point,event_range,off line_event,structure,offline_cross_ref"

Set oPKG = DTSGlobalVariables.Parent

Dim oDumpTask
Set oDumpTask = oPKG.Tasks(2)

Set oTask = oDumpTask.CustomTask
oTask.ProgressRowCount = 1000
oTask.MaximumErrorCount = 1000
oTask.FetchBufferSize = 300 '++ fetch 300 rows in a single operation
oTask.UseFastLoad = True
oTask.InsertCommitSize = 300 '++ when fastload=true, commit
300 rows at a time
oTask.ExceptionFileColumnDelimiter = "|"
oTask.ExceptionFileRowDelimiter = vbCrLf
oTask.AllowIdentityInserts = False
oTask.FirstRow = 0
oTask.LastRow = 0
oTask.FastLoadOptions = 2
oTask.ExceptionFileOptions = 1
oTask.ExceptionFileName = "C:\Temp\marathon_import_error.txt"

first_table = lcase(oTask.DestinationObjectName)


for each t_name in split(table_list_order,",")
'++ do not import table already specified in task1
if t_name<>first_table then
oTask.SourceSQLStatement = "SELECT * FROM " & t_name
oTask.SourceObjectName = ""
oTask.DestinationObjectName = t_name

Dim oTransformation
Dim oTransProps
Dim oColumn
Dim iColumn
Set oTransformation =
oTask.Transformations.New("DTS.DataPumpTransformCo py")
oTransformation.Name = "DirectCopyXform" & "_" & t_name
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

for iColumn=1 to oTransformation.SourceColumns.count
msgBox ("Source Table column count: " &
oTransformation.SourceColumns.count)
oTransformation.DestinationColumns(iColumn)
= oTransformation.SourceColumns(iColumn)
msgBox("iColumn - " & iColumn)
next


Set oTransProps = oTransformation.TransformServerProperties

Set oTransProps = Nothing

oTask.Transformations.Add oTransformation
Set oTransformation = Nothing

oTask.execute oPkg, Nothing, Nothing, CLng(0)

end if
next

' Clear Up
Set oTask = Nothing
Set oPKG = Nothing

Main = DTSTaskExecResult_Success
End Function
=========================

Allan Mitchell wrote:

Hello q_test,

Whay are linked servers not possible?

Why would my first suggestion not work and have the Wizard do the bulk of
the work?

Doing it the way you are trying can be done but will involve a lot of code.
I can point you/ Give you things that will help but it by no means the easier
route.

Allan

Allan,

Actually Linked server is possible, but it is very inconvenient: The
access db is collected from multiple sources, in fact, it may reside on
different computers. Linked server needs to be pre-created and it is
inflexible to change. DTS package is more flexible. You open package
from enterprise manager, right-click on the connection object, and
specify the Access data location, then execute the package, that's what
I am trying to achieve...

I did use the import wizard and generate 100 data pump tasks. However,
there is no way to tell the wizard which table to import prior to the
others (I want all the violation of constraints to be reported instead
of being ignored). In addition,when I need to move the package from dev
server to production server, or I have to locate the ACCESS DB in a
different directory, I have to change in 100 or 200 places.

That's my justification on attempting to write the code and use the loop
to simplify the process, but I cannot make it work now...

More help will most welcome. Thank you.


Allan Mitchell wrote:

Hello q_test,

Something for you to look at may be OPENDATASOURCE()

As I mentioned in the first mail the Dynamic Properties task will take care
of repointing your connections. The downside will be though the DestinationObjectName
being DB.OWNER.TABLE. You can write a piece of script that will get rid
of this and it is a one time only thing. You will need to handle the workflow
yourself and this will be a one time process as well.

Allan