September 16, 2004 at 10:32 am
Using a DTS Package is there a way to automate the running of a DTS Package that needs to get data from the only table in Access to SQLServer. The access database table will have a different name every month. I currently have a DTS Package that will grab the data in the table and put it into a SQL Server table. My problem is I would like to automate this but I need to set the table name every time in the transformation properties since the name changes every month. Can anyone help?
Tom
September 16, 2004 at 10:56 am
If you have some fix pattern for your table name like.
"fixString" & month & year
then you don't have to change the source table name in DTS package,whenever you change your table name in access.
- Kamlesh Agnihotri
September 16, 2004 at 2:54 pm
How do you change the Data transformation properties dynamically?
Right now I select from a drop down from the table/view drop down.
I would like to accept a variable somehow?
Tom
September 16, 2004 at 6:31 pm
You have to write everything in DTS ActiveX script, you can not use Designer tools. Inside the ActiveX script you can change all the properties dynamically. Let me know if you need more help.
- Kamlesh Agnihotri
September 17, 2004 at 7:10 am
Thanks for your help Kamlesh. Still not sure where I would write that script. Here is what I got.
1. Truncate the Vendor Table
2. On completion I have a connection properties object that links the
mdb.
3.Then I have a Transform Data Task Object where I map the fields in the table to the table in the SQL Server table in the db.
Also, how might that code look. The table is like this. FMH_MV dump file 04Sep6. Where the 04Sep6 changes from month to month.
Thanks
Tom
September 17, 2004 at 9:16 am
In an ActiveX Script, add the following code.
Add this task just before the connection object.
Function Main()
Dim oConn
Set oConn = DTSGlobalVariables.Parent.Connections("your_Connection_Name")
oConn.DataSource = DTSGlobalVariables("your_MDB_Name").Value
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
September 17, 2004 at 12:42 pm
Optionaly, you can use the "Dynamic Properties Task" in DTS. This isn't nearly as sophisticated as what you can do with ActiveX, but it's sufficient for setting connection variables.
I haven't worked with the Access ODBC Connection in DTS, so I can't help you there. This will take some experimentation, but it's probably worth a shot.
cl
Signature is NULL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply