?
Solved

can DTS perform dynamically???

Posted on 2002-06-28
4
Medium Priority
?
375 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 1200 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

752 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