Solved

can DTS perform dynamically???

Posted on 2002-06-28
4
361 Views
Last Modified: 2013-11-30
Hi I'm new to Data Transformtion Service, Does DTS can performall all transformation and act like a program?

What I meant is that does it able to ask you for database name, create differnt table upon falling to different logical formula, etc. like Visual Basic.  I know it's has ActiveX VB Script but it's only get data that are provide through the source and destination only, not able to twist the database. And does ActiveX Script able to execute SQL statement?

0
Comment
Question by:ASTronus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Expert Comment

by:curtis591
ID: 7117535
You can change any portion of a dts package from com objects.  You can call the program from Visual Basic, Foxpro whatever and you can access all the properties of the package.  The following code changes the source database that the data is coming from before it executes the package.  

dts_package = CreateObject("DTS.package")
dts_package.LoadFromSQLServer (server, user, password, , , , , alltrim(ppackage))

dts_package.Connections.Item(2).Catalog = database_name dts_package.connections.item(2).datasource = sever_name

dts_package.Execute

From my experience from using them I make the DTS Package do its thing with out changing any properties through activex script and I use my calling program to switch the databases and commands.  

To execute sql in an activex script you have to use a adodb connections and recordsets.  Just like visual basic.
0
 

Author Comment

by:ASTronus
ID: 7119658
if I have a complex structure like creating dynamic tables base on other tables,joining different fields together and perform calculation, can it be done done with ActiveX Script? which one is faster, ACtiveX script or Visual Basic coding,visual basic is more powerful does  they speed equivalent when import/export?

I haven't see ActivX script and any transformation that is consider dynamic yet, so don't know how limit is the DTS
0
 

Author Comment

by:ASTronus
ID: 7119661
where can i find more info. about that, I want to know can DTS can handling complex system with lots of logic. and does it faster or the same.
0
 
LVL 2

Accepted Solution

by:
PUMASOFT earned 300 total points
ID: 7121458
I have a similar problem where I am creating a DB and all of its objects, converting ISAM data to sql. I have on dts package that does the whole lot, including promting for database name location etc. I change the SQL statements dynamically using a VB task which is the first task.

here is the code

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

      Set oPack = DtsGlobalVariables.Parent
      bEncrypt = True
      iRecs = 0


'ACCEPT/CHANGE SERVER
      Set oConn = oPack.Connections(2)
      sString =  InputBox("Enter Server Name","CREATE  DATABASE", oConn.DataSource)
      If sString = "" or Len (sString) < 3 then
            Main = DTSTaskExecResult_Failure
            Exit Function
      End If
      oConn.DataSource = Ucase(sString)
      If LEFT(Ucase(sString),7) = "XXX" then
            bEncrypt = False
            If MsgBox( "Run Minimum TEST Conversion" ,vbYesNo , "Conversion") = vbYes then
                  iRecs = 100
            End If
      End If

'for iCount = 120 to oPack.Tasks.Count
'      Set oTask = oPack.Tasks(iCount)
'      MsgBox iCOunt & vbcrlf & oTask.Name & vbCrLf & oTask.Description
'Next

'ACCEPT/CHANGE DATABASE LOCATION IN CREATE DATABASE
      Set oTask = oPack.Tasks(10)
      Set oCTask = oTask.CustomTask
      iPlace1 = InStr(1,oCTask.SQLStatement,"SET @DRIVE = ")
      iPlace1 = Instr(iPlace1, ocTask.SQLStatement, "'") + 1
      iPlace2 = Instr(iPlace1, ocTask.SQLStatement, "'")
      sString = MID(ocTask.SQLStatement,iPlace1, iPlace2 - iPlace1)
      sNewDBName = InputBox("Create Database In Directory", "CREATE MADISUN DATABASE", sString)
      If sNewDBName = "" or Len (sNewDBName) < 2 then
            Main = DTSTaskExecResult_Failure
            Exit Function
      End If
      Sstring = "SET @DRIVE = '" + sString + "'"
      sNewDBName = "SET @DRIVE = '" + sNewDBName + "'"



'ACCEPT/CHANGE DATABASE NAME IN CREATE DATATBASE
      iPlace1 = InStr(1,oCTask.SQLStatement,"SET @DBNAME = ")
      iPlace1 = Instr(iPlace1, ocTask.SQLStatement, "'") + 1
      iPlace2 = Instr(iPlace1, ocTask.SQLStatement, "'")
      sString = MID(ocTask.SQLStatement, iPlace1, iPlace2 - iPlace1)
      sNewDBName = Ucase(InputBox("Enter Database Name", "CREATE DATABASE", "XXX"))
      If sNewDBName = "" or Len (sNewDBName) < 5 then
            Main = DTSTaskExecResult_Failure
            Exit Function
      End If
      sString2 = "SET @DBNAME = '" + sString + "'"
      sString3 = "SET @DBNAME = '" + sNewDBName + "'"
      oCTask.SQLStatement = Replace(oCTask.SQLStatement, sString2, sString3)

      For iCount = 1 to oPack.Tasks.Count
            Set oTask = oPack.Tasks(iCount)
            If Instr(oTask.Name,"ExecuteSQL") > 0 then
                  Set oCTask = oTask.CustomTask
                  oCTask.SQLStatement = Replace(oCTask.SQLStatement, "USE " & sString, "USE " & sNewDBName)
            End If
      Next

'CHANGE NAME OF DSN ON TRANSOFT CONNECTIONS
      Set oConn = oPack.Connections(1)
      sString = ""
      iPlace1 = 0
      Do Until sString = "DATA SOURCE"
            iPlace1 = iPlace1 + 1
            set oTask = oConn.ConnectionProperties(iPlace1)
            sString = UCASE(oTask.Name)
      Loop
      sString = UCase(sNewDBName) & "V"
      oTask.Value = sString

'IF DESTINATION DATABASE ON TRANSFORMATION OBJECTS
      For iPlace1 = 1 to oPack.Tasks.Count
            Set oTask = oPack.Tasks(iPlace1)
            sString = UCase(oTask.Name)
            If Instr(1, sString, "DTSDATAPUMPTASK") > 0 then
                  Set oCTask = oTask.CustomTask
                  ocTask.UseFastLoad = true
                  ocTask.FastLoadOptions = 0
                   ocTask.LastRow = iRecs
                  ocTask.InsertCommitSize  = 1000
                  sString = ocTask.DestinationObjectName
                  sString = "[" & sNewDBName & "]" & Right(sString, Len(sString) - Instr(1,sString, "]"))
            End If
      Next


'CHANGE/AMEND ENCRYPTION SYNTAX - ENCRYPT IF SERVER IS NOT XXX
      Set oTask = oPack.Tasks(1)
      Set oCTask = oTask.CustomTask
      'Remove any accidental commenting out of encryption anyway
      oCTask.SQLStatement = Replace(oCTask.SQLStatement, "/*WITH ENCRYPTION*/", "WITH ENCRYPTION")
      sString =  oConn.DataSource
      If bEncrypt = FALSE AND msgBox( "Run Enqrytption" ,vbYesNo , "Conversion") = vbNo then
                         oCTask.SQLStatement = Replace(oCTask.SQLStatement, "WITH ENCRYPTION", "/*WITH ENCRYPTION*/")
      End If


      Set oCTask = Nothing
      Set oTask = Nothing
      Set oPack = Nothing

      Main = DTSTaskExecResult_Success
End Function




0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Star schema daily updates 2 37
Many to one in one row 2 39
Need age at date of document 5 17
T-SQL: problem comparing datetime 4 48
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question