August 20, 2007 at 6:20 am
Dear Forum,
I want to automate the export of a view to the correct .mdb file (access db).
The view is a variable, as is the path of the .mdb file.
For example;
View factuurgegevens_9 has to be exported to c:\DB_PRESMON_DWH_9\factuurgegevens.mdb
View factuurgegevens_10 has to be exported to c:\DB_PRESMON_DWH_10\factuurgegevens.mdb
I know u can use global variables in a package and activate the package through a stored_procedure. The stored procedure sets the variables in the package through axtiveX.
An example of activeX could be:
'**********************************************************************
' Visual Basic ActiveX Script VERVERS_KUBUS
'************************************************************************
Function Main()
Dim objTask
Dim objProps
Set ObjTask = DTSGlobalVariables.parent.Tasks("Verversen_kubus")
Set ObjProps = ObjTask.Properties
' De gehele database wordt geselecteerd
ObjProps("TreeKey").Value = "LOCALHOST\cube_Presmon_" + DTSGlobalVariables("Klantnummer").value
Set ObjProps = nothing
Set ObjTask = nothing
Main = DTSTaskExecResult_Success
End Function
Im not familiar with this syntax. Suggestions for the above desirable functionality are welcome.
Greets,
Ben
August 20, 2007 at 8:25 am
This could be more complicated than you expect or simple. Are you looking to move the contents of a particular view to a particular file each time? Or does this change?
If xx_9 goes to one file and xx_10 goes to another, why not set connections to each of these and set a transform for the particular view to the particular file?
Doing this programatically will get complex as you need to create the MDB file and then move data. Scripting here isn't necessarily what you want. You might want to use the Dynamic Properties task instead to reset the connections and transform as needed.
August 20, 2007 at 12:32 pm
The platform is SQL2000, but the move content action from view_x to file_x has to take place everytime the user askes for it from within a asp.net application.
A new customer_y can buy a subscribtion for this asp.net application. A new database_y will then be created, and a new view_y.
The new customer_y can make an export from the view_y to the file_y any time he or she wants. All from within the asp.net application.
The transformations between source and destination do not change, nor does the relation between view and file. However new connections are created for each new customer.
Greets,
Ben
August 21, 2007 at 11:01 am
Ben, it sounds that, for a given customer, you will always use the same view and the same target file. What that means is that you need to capture the relationship between a customer and their unique 'names' to use for the exporting. Stored in a table, along with any special connection string settings or other environmental settings, could make it very straightforward to compose the job that you then use DTS for exporting.
Are your questions answered yet?
August 21, 2007 at 12:58 pm
Each customer has its own view, its own database and its own file.
I used the dynamic properties task so i dont need to program in AxtiveX.
Furthermore i used a sp to assign the global variables .
I used the article below to make a dynamic DTS package:
http://www.sqlservercentral.com/columnists/bknight/dynamicpropertiestask.asp
I have it working now..
thx.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply