DTS package
-
Hello there!
I have this program in VB 6 that extract data. The processing is quite slow because there are thousand of records. In my program, it will call a stored procedure from DATABASE A and its result will be inserted in DATABASE B. Im thinking if I can do this in DTS instead and just call the package in my program in order to fasten the process.
Is it possible? Can anybody show me how? Or is there any alternative way?
Thanks in advance.
-
I think that DTS can be used to improve the performance of the load process you are doing. You are probably loading a single row at a time which can be quite slow. DTS can be used to insert large volumes of data very efficiently in a similar way to what BCP does.
To do this
1. Create a new package (in Enterprise Manager)
2. Create a connection to the source database
3. Create another connection - to the destination database
4. Drag a "Transform Data Task" onto the DTS design surface. This will prompt you for the source and the destination connectsions
5. You should now see a black line joining the two connections
6. Right click on the line and select properties
7. Selec the "SQL Query" radio button in the source table
8. Type the SQL code required to run the stored procedure that extracts the data
9. Click on the destination tab and select the table that is to receive the data
10. Click on the Transformations tab. If the column names from the stored procedure and the destination table are the same, DTS will automatically join them up. If not, you will need to do this (select the source column and the destination columns, click on New and, from the list that pops up, choose "Copy Column")
11. Click OK
12. Save the package.
You can now execute the package.
I have assumed that there is no other processes needed to be run as part of this load. If there are, DTS can also be used to do those. e.g. if you need to delete data from the dsstination table, you can use the "Execute SQL Task" to do this.
-
hello... thank you so much I really appreciate your help. But I still have one more question. How can I handle if I have a parameter that I need to pass? Because in my program, the user will enter a week ending and it will pass to the stored procedure to search for that specific week ending. Can I also do this in the DTS?
-
Yes
First you need to create a global variable to hold the week number. To do this, select "properties" from the "Package" menu. Select the Global Variables tab and add your variable with some appropriate value. This will need to be updated when the package runs.
In the source you will have something like
Exec StoredProcedureName
Change this to
Exec StoredProcedureName @WeekNumber = ?
Then click on the parameters button and set the parameter mapping.
Now, how to set the week number. If you run this package interactively (from Enterprise Manager) change the value from the package properties.
If you run this package from SQL Agent, have a look at DTSRUN in Books Online. The "/A" command line switch is what you will need to use.
-
hello.. i will run the package from my VB 6 program. My code will go like this:
For calling the DTS:
strServer = "10.160.10.28"
strUsername = "fduser"
strPassword = "fduser"
strPkg = "dtstmpFDimp"
oPackage.LoadFromSQLServer strServer, strUsername, strPassword, _
DTSSQLStgFlag_Default, "", "", "", strPkg, 0
oPackage.Execute
oPackage.UnInitialize
Set oPackage = Nothing
I'm a little confuse on how I will pass my parameter to the DTS package.My understanding is the week ending that the user will enter in my program will automatically pass on the DTS once I call the package. Just like when calling the stored procedure.
But when I run the package, I still need to put the specific week ending date on the code itself
Exec StoredProcedureName @WeekNumber = '2007-03-10'
Actually, when I click the parameter tab, it prompted me "sql staement does not contain any parameter". I'm a liitle lost.
I really appreciate your help again. THanks
-
The question mark "?" in my post was not a typo - the string needs to be
Exec StoredProcedureName @WeekNumber = ?
To pass the week number in from you VB 6 program, you need to use the "GlobalVariables" collection to assign the value specified by your user to the variable inside the package. Not sure of the syntax you will need but it will be something like
Set objGlobal = objPackage.GlobalVariables("WeekNumber")
objGlobal.Value = "2007-03-10"
-
Yes, thank you got it now
. I'll check on the syntax in VB. And I hope you don't mind if I will raise again some question if I have.Thank you so much. I really appreciate it.
-
I would suggest that the first stored procedure get changed. Instead of just returning the result, let it update database B. That should be faster than using DTS.
5ilverFox
Consulting DBA / Developer
South Africa
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply