DTS Scripts (SQL Server 2000)

Hello,

I would like to know the DTS Scripts for converting Access Databases to SQL Server 2000. The Access Databases are located on multiple servers. The tables should be created the first time. Then the data should be copied from Access Databases located on multiple servers. No duplicate rows should be added to SQL Server tables. The data should be copied from Access Databases every 15 minutes. Please show me a example (code in DTS Scripts) which does this task.

Thanks,

Vivek Gupta

You can use import export wizard in SQL enterprise manager and it will create DTS package for your, next you just have to modify it to accept input access file name as parameter, and maybe table name in it? But if your table structure is different in each database it will be hard to create single DTS package because DTS package will store table structure definition as fixed. Maybe instead of this you can write simple .Net application which will do this for your for any Access database location and table name.

Thanks

DTS Restore from a file

We have a production server and a bunch of development servers. The
Production server is located in a NOC (Network Operations Center)
facility and is physically unaccessible on a regular basis. There are
DTS packages on the production server which we'd like to pull down to
our Dev servers.

I was able to save the DTS package to a file and downloaded it to our
Dev Servers. But how in the world do I restore it?? I'm a fairly
technical guy and for the life of me, I'm not able to figure this one
out! How do I restore the DTS package which has been stored into a
..dts file?

Please help.

Thank you in advance,

-Umar.I think what you want to know is this:

Right-click on the "Data Transformation Services" FOLDER icon and select
"Open Package..." from the options. This brings up a dialog box to hunt for
your DTS file(s).

"Umar Farooq" <UmarAlFarooq@.gmail.com> wrote in message
news:1110475810.551932.46600@.f14g2000cwb.googlegro ups.com...
> We have a production server and a bunch of development servers. The
> Production server is located in a NOC (Network Operations Center)
> facility and is physically unaccessible on a regular basis. There are
> DTS packages on the production server which we'd like to pull down to
> our Dev servers.
> I was able to save the DTS package to a file and downloaded it to our
> Dev Servers. But how in the world do I restore it?? I'm a fairly
> technical guy and for the life of me, I'm not able to figure this one
> out! How do I restore the DTS package which has been stored into a
> .dts file?
> Please help.
> Thank you in advance,
> -Umar.

DTS package and Firewall

Hi all, I have some DTS packages that are used to import /export data to a
SQL box located outside in a DMZ behind a firewall.

We need to open up a port in the firewall so that the Internal Server can
communicate (Execute DTS packages against) with the SQL box located outside
the DMZ.

How do I find out what Port we need to open so that the Internal SQL box can
communicate with the external SQL box?

Thanks in advance
Mark"Mark" <markjones@.n0Sp8mTAIRAWHITIdotAC.NZ> wrote in message news:<ZFxoc.4117$XI4.156748@.news.xtra.co.nz>...
> Hi all, I have some DTS packages that are used to import /export data to a
> SQL box located outside in a DMZ behind a firewall.
> We need to open up a port in the firewall so that the Internal Server can
> communicate (Execute DTS packages against) with the SQL box located outside
> the DMZ.
> How do I find out what Port we need to open so that the Internal SQL box can
> communicate with the external SQL box?
> Thanks in advance
> Mark

In general, a DTS package is like any other SQL client:

http://support.microsoft.com/defaul...2&Product=sql2k

If you have a variety of tasks in your package (ActiveX, FTP etc.)
then you might require additional ports to be opened, but that depends
on exactly what those tasks do, what protocols they use etc.

Simon

DTS Oracle Connection - Importing Data

I have SQL Server 2000 and am using DTS to simply import some data located on a remote Oracle DB.

If I have the following information:

TNS Name:

 ThisApp.world
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS =
 (COMMUNITY = tcpxxx.world)
 (PROTOCOL = TCP)
 (Host = SomeHost)
 (Port = 1234)
 )
 )
 (CONNECT_DATA =
 (SID = App)
 (GLOBAL_NAME = ThisApp.world)
 )
 )

And I also have the name of the Table.

Using the DTS Wizard, on the Choose a Data Source screen, I pick the following:

Data Source: Microsoft ODBC Drive for Oracle
Server:
Username:
Password:

I don't know what I should put into the other fields, given what I have above, i.e. what to put in for Server, Username, and Password! Or should I use a different procedure to import this Oracle data? I am almost positive I was not supplied the username/password. I was told I could connect given the above TNS information. What do you all recommned?

Your assistance will be greatly appreciated.Given the above information, how can I use Server Explorer in VS.NET 2002? It still prompts me for a Username/Password, which I do not have.

Please help anyone.

DTS Missing Field

i've run into a problem with running a dts job that imports an access db
into sql located on same win 2003 server. the database has no relationships
and has
worked fine until i recently added a new field in a table in access.
crazy as it may sound, the dts job moves every field over fine except the
new field. I tested the job by creating a new table altogether and it
transforms
fine.
there is nothing special about the data type, etc. it's just another integer
field like other existing fields.
Where can I look in a log for a detailed description of a job? is it from
inside QA?
I should mention that my dts job is dropping and recreating each table in
sql 2000.Hi Scott,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
As my understanding of your information, you have a DTS package run as a
job. Then DTS package will import the data from a access database to an SQL
Server database. It runs fine in the past. Then you add a new column in the
Access database table then when the job runs, you found that the data in
the new add column is not imported to the SQL Server, right? If I
misunderstood, please feel free to let me know.
Based on my experience, after you design the DTS package, when there is
some modification on the table, such as add a column or delete a column,
it will be reflected in the old DTS package. However, the data transform
between the database should be specified. It could not be specified in the
DTS package automatically.
So, in your Enterprise Manager, please open the DTS package in the design
window, and right click the Transform Data Task ( which is the arrow link
the transformation data source( access database) and the data destination
(SQL server database)), choose 'Properties'. In the Source tab and the
Destination tab, you will notice that the new added column is there in both
table and then in the Transformation tab, you will see the transformation
of the columns between the source and the destination. Then, you will
notice that there are no transformation between new-added columns in the
source and destination. Please click the 'New' button and choose the 'Copy
column', then press OK. Then there is a new window titled 'Transformation
Options' prompt, you should specify the source column and the destination
column, which are the new added columns. Then, you could run the DTS
package and check if the DTS package execut and got the expected
transformation. Then you could run this DTS package as your job.
You could check what the transformation is doing by checking the properties
of the destination and the source and the transformation in the DTS package
design window and use the Profiler of SQL Server to catch what was
executed. For your case, I would recommend that you should check that if
the new-added columns' transformation is added in the DTS package.
Hope this helps and if you still have questions, please feel free to post
new message here and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi Scott,
I am reviewing your post and since we have not heard from you for some
time, I wonder if my answer is helpful in solving your problem or if you
still have any questions that we could help. For any question, please feel
free to post message here and we are glad to help.
Thanks
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.