Solved

can DTS perform dynamically???

Posted on 2002-06-28
4
355 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
  • 2
4 Comments
 
LVL 6

Expert Comment

by:curtis591
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now