?
Solved

can DTS perform dynamically???

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

616 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