Re: import 100+ tables from ACCESS to SQL Server w/ same structure

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



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,

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

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,offline_event,structu
re
,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.DataPumpTransformCopy")
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,

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

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.



.

Relevant Pages

  • Re: fill dataset/grid with multiple queries from multiple servers
    ... > Dim dsXMLData As New DataSet ... When the app first opens I get a + sign on the datagrid,> clicking that I get the word table and clicking that I get to the data, any> ideas how to get straight to the data? ... >> or just use the same connection itself but the data source portion of the>> connection string would be referencing the server name as a variable that> is ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Best way to communicate from Desktop to SQL DB
    ... And for the connection strings: ... to a remote server. ... Dim cmd As New SqlCommand ... Dim ProductID As Integer = CType, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: setup an offline connection
    ... If you are calling the package in VB then you can simply use the object ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... > the laptop to a client, I would like to modify the DTS package to ... > delete the connection to my computer and put the connection to their ...
    (microsoft.public.sqlserver.dts)
  • Re: create dynamic dts package
    ... In my reply I said that all this may not be necessary and you may be able to simply rebuild the Transform data Task in an existing package. ... Dim oConnProperty As OleDBProperty ... ' create package connection information ... Dim oConnection As Connection2 ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Package Connection Properties Access Error
    ... When a DTS package is designed the GUI ... shows Connection 1 as the source node, Connection 2 as the destination node ... If I design ... Then it is looking for a server connection that is not visible from your ...
    (microsoft.public.sqlserver.dts)
Loading