How to create a table in DTS pack dynamically.
-
Hi,
I have to create a table in dts packages, I used to hard code the create table script in the exqcute SQL task
but now I shd create the table by using the script which shd be genric,
like the code shd be in such a way taht if I pass orders, it shd create table orders using orders.sql
or if i want to create customers table then it shd execute customers.sql task.
so what function or task ican use in the dts packages so that the table would be created on fly when I pass the script name.
thanks.
-
You could use an ActiveX script setup in either 1 of two ways:
Both ways involve the ActiveX script evaluating the Global Variable of the table that you wish to pass in (i.e. orders). Then:
Method 1 - One ActiveX task followed by One ExecuteSql task
The ActiveX script contains all of the scripts stored in dimensioned variables to create the different tables, one of these variables is then used as the Command in the ExecuteSql Task. For example:
Function Main
Dim oPkg, oTsk, oCusTsk
Dim sOrderSql, sCustomerSql
Set oPkg = DTSGlobalVariables.Parent
Set oTsk = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1")
Set oCusTsk = oTsk.CustomTask
sOrderSql = "CREATE TABLE order( orderID INT, itemID INT .....)"
sCustomerSql = "CREATE TABLE customer( customerID INT, first_name VARCHAR(30) .....)"
s.....Sql = "CREATE TABLE ....."
If DTSGlobalVariables("gvTableName").Value = "Orders" Then
oCusTsk.SQLStatement = sOrderSql
ElseIf DTSGlobalVariables("gvTableName").Value = "Customers" Then
oCusTsk.SQLStatement = sCustomerSql
ElseIf .....
And so on.
Method 2 - One ActiveX task followed by multiple ExecuteSql tasks
The ActiveX script in this case evaluates the global variable and then enables and disables the following SQL tasks accordingly. For example:
Function Main
Dim oPkg, oStp
Set oPkg = DTSGlobalVariables.Parent
If DTSGlobalVariables("gvTableName").Value = "Orders" Then
' Set orders task to waiting
Set oStp = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")
oStp.ExecutionStatus = DTSStepExecStat_Waiting
'Customers Inactive
Set oStp = oPkg.Steps("DTSStep_DTSExecuteSQLTask_2")
oStp.ExecutionStatus = DTSStepExecStat_Inactive
ElseIf DTSGlobalVariables("gvTableName").Value = "Customers" Then
' Set orders task to inactive
Set oStp = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")
oStp.ExecutionStatus = DTSStepExecStat_Inactive
'Customers Waiting
Set oStp = oPkg.Steps("DTSStep_DTSExecuteSQLTask_2")
oStp.ExecutionStatus = DTSStepExecStat_Waiting
ElseIf .....
And so on.
Hope these help!
-
Hi Adrian, thanks for u r response, but I do not have two or 3 tables, I have 400 to 500 tables whose modified scripts are stored in one shared folder, I have to get the script of that table whose table name is entered in the global variable.
-
400 to 500, hmm.....
I guess I need a Method 3 then!!
If you are passing the script name as a Global Variable you could use the FileSystemObject to read the file in to another GV or dimensioned variable and use that to set the Statement property of the ExecuteSQLTask.
Function Main
Dim oPkg, oTsk, oCusTsk
Dim fso, f
Dim sScriptText
Set oPkg = DTSGlobalVariables.Parent
Set oTsk = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_1")
Set oCusTsk = oTsk.CustomTask
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(DTSGlobalVariables("yourScript").Value, 1) 'For Reading
sScriptText = f.readall
oCusTsk.SQLStatement = sScriptText
Set f = Nothing
Set fso = Nothing
Set oCusTsk = Nothing
Set oTsk = Nothing
Set oPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
-
Thanks for u r reply, I will try this one today.......
-
I have a folder c:\servername\Project\dbo.orders.tab
all the tables scripts are stored here in the project folder which is a shared one.
Now I have to call this files and open them, so this file contains the script to create a newtable say create Orders(......)
so after opening the file I have to replace the table name and all occurances with orders to orders_test even the constraints also
and then after renaming the table name n the constraints I have to execute that sql task so that the table is created on fly with table name orders_test and any constrainsts with Pk_orders_test.
so how can I achieve this task using the active X script.
thanks,
-
The replace function is available in ActiveX; You can use it after you've read in the file to a variable. Usage:
Replace(myscript, "orders", "orders_test")
^ ^ ^
Look In Find Replace With
This will replace any occurences of orders with orders_test.
-
Thanks for u r reply, greatly appreciate u r help.
-
Hi Can Any one help,
I am new to this forum, so not sure if its the write place to post a question.
Well i got a flat file (in{CR}{LF} format) and needs to be imported into a sql server 2000.
now i need to know how can i create a package using DTS or some thing which kindoff breaks the data down and insert it into appropriate tables from the flat file. I need to find a way to carry it out in one process as the data is imported through the pipe. SO i have multiple tables which would eventaully contain the data from flat file after two weeks. is there any way i can automate the process.
regards
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply