I am tasked with downloading from an FTP site, a flat file (an inventory of
loan files) that's about 220mb large.
I then rename it to a text file and import it into an Access database. This
database is used in 2 different applications, the first is used in Word where
we have a menu of templates that users pull and after inserting a loan
number, information from that loan is blown into the template from the table
in the database.
The second application is an SQL database (SQL 2000) where the users have a
front end client used for making transactions on these loan files.
So on the SQL portion, I first truncate the SQL table, then run the DTS
wizard and import the new table from the Access database.

So here I am right now in Enterprise Manager, trying to figure out how to
build this Local Package to do this for me automatically every night. I've
figured out the first portion, pulling in a FTP task and setting this up.
But I'm lost on how to proceed next.

Can anybody walk me through the steps for getting this done?

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

On Sep 17, 6:47*pm, Rockitman <Rockit... (AT) discussions (DOT) microsoft.com>
wrote:
Hi,
if i understood correctly you have to move one single table from an
Access DB to a SQL DB.
If so, then on your DTS Designer window you should see on the left
connections and tasks panels (if not there's the menu).
Click on the Access connection icon (Access97 icon) to build a
connection to your data source.
Click on the OledbProvider for SQLserver connection icon to build a
connection to your target SQLserver DB.
Then select the source connection and in the task panel (or menu)
choose "Transform Data Task". Your cursor should change and a message
tells you to select the destination connection. Once you click on the
destination connection a grey arrow should link the two connection
objects.
Double click on it to set columns and transformation.
DTS are quite easy and intuitive, but if you need more help, just let
us know

HTH
M.

Intuitive to you maybe, it's Greek to me!!

before you get to far ahead of me, remember I first need to automate the
process of bringing in the downloaded file into the Access table in the first
place.
I've finally muddled through using Text File as my Source Connection and
Access as my destination using the Transform Data Task. The file is fixed
and I set all the columns, still confused on the Transactions tab and how to
setup the source columns to the destination columns.
Anyways, when I execute it seems to work. Only problem is that my Access
file is much larger than when I do this stuff manually. Need to figure out
why this is so.


"matteus" wrote: