1cell
asked on
DTS Package w/ Dynamic MDB File
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???
How are you trying to use the global variables? Dynamic Properties task? ActiveX script?
ASKER
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.
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.
ASKER
sorry again, the parameter in that query is the OfficeID global variable.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
If you double click on the connection, it's the text in the "existing connection" box...
ASKER
Haha! I love learning like this! Thanks for the help!
" I love learning like this!"
Ah ya, everyday is one big learning experience :)
Ah ya, everyday is one big learning experience :)