Solved

DTS Package w/ Dynamic MDB File

Posted on 2004-04-13
8
502 Views
Last Modified: 2012-06-21
I've got a DTS package that's supposed to get a few global variables and export data to an Access MDB file defined by one of the variables.  The problem I'm having is that I can't get it to export to the right MDB file.  It's constantly going for the one defined in the Access Connection instead of the one defined in the Global Variable.  How do I make the DTS Package work with the MDB File path passed through the variable???
0
Comment
Question by:1cell
  • 4
  • 4
8 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10816697
How are you trying to use the global variables?  Dynamic Properties task? ActiveX script?
0
 
LVL 6

Author Comment

by:1cell
ID: 10816748
OK, Sorry.  Here's a little more info

First I've got a dynamic properties task that gets the global variables from a stored procedure.
Variables:
OfficeID
MDBFile
ExportID

Then there's an SQL Connection with a Transform Data Task to the Access connection
select top 30000 homephone
from tbl_template where regionID = ? and exportID is null

That's where I need to use the MDB File located in the path defined by the MDBFile variable value.

I do this in another package that works fine.  Can't figure out why it's not working here.  The only real difference is that the MDB File in this situation is the destination whereas the other situation uses it as a source.
0
 
LVL 6

Author Comment

by:1cell
ID: 10816757
sorry again, the parameter in that query is the OfficeID global variable.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 34

Accepted Solution

by:
arbert earned 125 total points
ID: 10816918
I've had problems before where DTS doesn't actually change the parm and it uses the value that's already in the connection.  You can code this in your main script (or add and ActiveX script task and make it execute first with workflow):

Dim oConn, sFilename


      Set oConn = DTSGlobalVariables.Parent.Connections("NameOfYourAccessConnectionHere")
      oConn.DataSource = DTSGlobalVariables("MDBFile")
      Set oConn = Nothing

      Main = DTSTaskExecResult_Success
0
 
LVL 6

Author Comment

by:1cell
ID: 10817028
I guess I don't know where to find the connection name.  It's not just "Microsoft Access" as listed on the little icon.  Or if it is, I'm getting a runtime error anyway.
0
 
LVL 34

Expert Comment

by:arbert
ID: 10817052
If you double click on the connection, it's the text in the "existing connection" box...
0
 
LVL 6

Author Comment

by:1cell
ID: 10817364
Haha!  I love learning like this!  Thanks for the help!
0
 
LVL 34

Expert Comment

by:arbert
ID: 10817600
" I love learning like this!"

Ah ya, everyday is one big learning experience :)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question