How to close a DTS connection within the package? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

How to close a DTS connection within the package?

Author

Topic

Zathras
Starting Member

28 Posts

Posted - 2004-06-17 : 16:18:43

I've got my DTS package running that exports SQL Server data to a MDB file. The last step in the workflow is a AVBScript Task to compact the MDB:


Function Main()
  Dim jro, fso
  Dim strSQL1, strSQL2, strFile1, strFile2

  Set jro =  CreateObject("JRO.JetEngine")
  Set fso = CreateObject("Scripting.FileSystemObject")
  strFile1 = DTSGlobalVariables("FileLocation").Value
  strFile2 = Left(DTSGlobalVariables("FileLocation").Value,Len(DTSGlobalVariables("FileLocation").Value)-3) + "tmp"
  strSQL1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile1
  strSQL2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFile2

  jro.CompactDatabase strSQL1, strSQL2
  Set jro = Nothing

  fso.DeleteFile(strFile1)
  fso.MoveFile strFile2, strFile1

  Main = DTSTaskExecResult_Success
End Function

The step fails becasue the MDB file is still open from the DTS's connection (the DTS connection uses Jet's Exclusive Mode). How do I close the DTS's connection to the MDB file so I can compact it? I tried a:
DTSGlobalVariables.Parent.Connections("Microsoft Access").Close
But that returns an error saying the Close method does not exist.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-17 : 19:43:56

Maybe another way around it:

You can specify that the MDB is automatically compacted on close. Although I'm not sure if this still applies when opening the MDB via OLEDB/ODBC.
The option can be set in Access in Tools->Options->Compact On Close.

Zathras
Starting Member

28 Posts

Posted - 2004-06-17 : 20:02:44

Can't do that... the MDB is created via DTS Jet code when it doesn't exist.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-06-17 : 20:41:17

Then why do you need to compact it?

You should be able to set the Jet OLEDB parameters when you're creating the MDB file. Not sure how you'd go about it, but it must be possible.

Another alternative is to have a 'template' MDB file that already has the Compact On Close flag set.

Zathras
Starting Member

28 Posts

Posted - 2004-06-17 : 21:28:21

If the MDB file already exists then the DTS just deletes and add data, so it grows.

I am loking for a DTS solution to close the connection.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2004-06-19 : 18:49:47

Try this. Create a dts package that creates the mdb. This dts package can be called from a different dts package that has 2 steps: step 1 is calling the 1st package and step 2 is your activeX script task that compacts the mdb

Zathras
Starting Member

28 Posts

Posted - 2004-06-19 : 22:01:31

Excellent idea; I didn't think of that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources