Acess Import Specification to SQL Server DTS

[Neilf]

I need to copy a large access db to SQL Server 2000 & want to copy the access import specs to SQL DTS packages. The spec data in access is held in the hidden system tables MSysIMEXColumns and MSysIMEXSpecs - is this data held in a similar manor in SQL Server? I'm guessing it must be somewhere in the master database but as yet can't find where.

Can anyone point me in the right direction? Thanks in advance.

 

[tlbroadbent]

DTS packages are stored in the msdb database. There are several related tables including sysdtspackages.

I've never heard of anyone wanting to use Access Import specs in SQL Server. I don't even know if it is possible. I have serious doubts. However, it seems like a hard way to create a package when the Import/Export wizard will do it all for you. It is very simpe to create a text file import to SQL Server. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Neilf]

Thanks for the advice - i've just found the dts tables in sql and i think you're right i can't see anything which looks the same as the access spec data but i'll keep trying. The reason i want to copy the specs is that they are for many undelimited text files with about 100 columns in each, so using the import wizard will not be quick - the work has already been done in Access.

 

[DrewConn]

I have had to port a lot of data over from Access to SQL. Rather than redefing the import specs, just import the table into Access with your already created specs.

Once the table is in Access then export the table as a comma delimeted text file. Then you can easily use the DTS wizard in SQL reading the comma delimeted file.

Hope this helps.

DrewConn