?
Solved

Importing data from a file to a table where filename is based on data in a table

Posted on 2004-11-29
13
Medium Priority
?
451 Views
Last Modified: 2013-11-30
I have been researching for the pass two weeks and I am unable to find the code that will pass a filepath and filename (which are stored in a table) open that file and import the data plus 1 field for the project number(is the filename) into an existing table.  So far I have a DTS package that determines which files are needed to be imported and updating the ProjectDemand table.  What I am confused on how to pass as a variable the filename and then have DTS import that data into the ProjectDemand table.  I have looked ta the code on SQLDTS.com (Looping, Importing and Archiving) unfortunately I get a "Bad Directory" error and I am unable to find a solution to correct this isssue, so I am hoping somenone has a better idea.  I tried setting the global variables on the DTS package, but how to I tell the DATA Drivien Query tool to use the varialbe in the path for the source.

Thanks,
Karen
0
Comment
Question by:Karen Schaefer
  • 7
  • 6
13 Comments
 
LVL 14

Expert Comment

by:adwiseman
ID: 12699816
Have you tried using active X scripting in the DTS package to set the source file.  You can use a SQL Command task, with a query that gets the next file to process, pushing that into a global variable.  Then using active X script, set the source file ne of the DataPump.  A dynamic properties task may also work.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12699903
Instead of looping inside of the DTS package, Consider writing a Stored Procedure, that calls a DTS Package using master..XP_cmdshell, passing in the file name as a global variable, setting your connection info on the tasks using a dynamic properties task.  Then you can loop inside the stored procedure, calling the DTS package once for each file.
0
 

Author Comment

by:Karen Schaefer
ID: 12700066
I have already created the process in DTS for the determination of the List of files to be used -   What I am unsure of the is the coding to acces those files, import the data into an the ProjectDemand table.

Could you give me sample code that might point me in the right direction.  Yes I have tried an Activex approach with any success.  Are you familiar with the SQLDTS.com code for Looping, Importing, and Archiving.  This code is very close to what I need, unforunately, I get a BAD Directory error and I am unable to find the solution to correct this issue.

Keep in mind that I am very new to SQL and DTS, so if you have samples that I might use, it would be greatly appreciated.

Karen
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Comment

by:Karen Schaefer
ID: 12700103
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002

I want to access the info in CADFIlePath and then open the first record's text file and import this data into my Project Table, then repeat the process until .EOF.
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12700190
If you are access a directory on the network, what user id is being used to access this network drive.  If it is a local SQL user, then the user the Server is running under needs access to this network drive.  If you use NT Authentication, the same rules apply.  If the user does not have access, then you would get an invalid file or directory path error that you are receiving.  What type of user is your connection running under, and does it have access to the network path?
0
 

Author Comment

by:Karen Schaefer
ID: 12700568
It is not the directory acess, I am able to import the actual files into my database - the problem lies in passing the filename as a variable and then accesing that file to import the data into my ProjectDemand table.

I have already created the process in DTS for the determination of the List of files my creatind a directory list using master xp_cmd...  to be used -   What I am unsure of the is the coding to acces those files, import the data into an the ProjectDemand table.

You would think this would be an easy task - If I was doing this in MSAcces I would have created an open recordset, pass the variable and created a temp link to that table and import the data into my new table(Project demand) - doesn't Sql or DTS have his function?  I just need to determined the source file and update the destination file - isn't there some code that can be used to accomplish this.

Thanks,
Karen


0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12700795
So you have a SQL Table with the path and name of each file, you want to run the same transform data task on each file into a project table, with 1 field being the projectnum(filename), and other fields from the text file.

The example on SQLDTS.Com loops through a directory, moves the file to an archive.  So the next time it looks in the directory the first file it finds to move is different, untill all the files have been archived.

we want to look at CADFilepath, process each file, and I assime flag the file as processed, and then do the next one.

In a DTS Package, Similar to Loop Import and Archive

Step 0: Execute DTS - Execute the DTS or Execute steps to get Directory Listing of files to process

Step 1: Execute SQL Tast - Mapping pathname, projectnum as out Parameters to global variables

   SELECT TOP 1 pathname, projectnum
   FROM CADFilepath
   Where Processed = 0
   Order by projectnum

step 2: activeX script - Check to see if the global variable projectnum was populated, if soo continue, else exit loop, all files processed

step 3 Dynamic Properties Task -
   
   Set the Text File Source Object to the global variable with the pathname

step 4 Transform Data Task - Between the Text File Object and the SQL server Database Object, defined with the proper transformation.  If you want to get the global variable into the destination table ov every record being imported, then use an ActiveX Script for the Transformation.

Step 5: Execute SQL Tast - Pass in Global Variable projectnum for ? Parameter Input

   UPDATE CADFilepath
   Set Processed = 1  
   WHERE projectnum = ?

Step 6 Execute activeX - Loop around  Use the SQLDTS.Com script for an example of looping to loop back to step 1  Re Initialize the globalfilepath fariables set in step 1


Does this help you through the thought process.
I would start by writing a dts that imports just 1 file based on the record in a table, then add the looping, that changes the file to process on each loop.
0
 

Author Comment

by:Karen Schaefer
ID: 12700925
the problem with the SQLDTS.com code is that I get a "Bad Directory" error - do you know the work around?

Karen
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12701018
Make sure both the source directory and the archive directory exists.  Change the global variables for these 2 parameters, and make sure they end in "\".  
I got it to work ater ensuring those 2 things.
0
 

Author Comment

by:Karen Schaefer
ID: 12701083
is there a way to attach files to this forum - if not can I email a screen shot of what I am currently using?
0
 
LVL 14

Expert Comment

by:adwiseman
ID: 12701123
Send me a screenshot, and a copy of your DTS package so far if you like to adwiseman at netzero dot com (In the correct format of course, I don't want bots spamming me)

For any one else monitoring this post, I'll make sure any pertanant scripts from the Final DTS Packages gets posted as vb create scripts.
0
 
LVL 14

Accepted Solution

by:
adwiseman earned 2000 total points
ID: 12706019
sQL Support Script

create table CADFilepath(projectnum VARCHAR(50), pathname VARCHAR(1000), processed BIT default(0))

go
create procedure CADFilePath_sp
as
insert CADFilepath(projectnum, pathname, processed)
values('test1.txt', 'c:\temp\test1.txt', 0)

insert CADFilepath(projectnum, pathname, processed)
values('test2.txt', 'c:\temp\test2.txt', 0)

go
Create table ProjectDemand (projectnum varchar(50), textfield1 varchar(1000))
go




DTS VB Script


'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: UpdateCAD_ProjectDemand_Mod.bas
'Package Name: UpdateCAD_ProjectDemand_Mod
'Package Description: UpdateProjectDemand
'Generated Date: 11/30/2004
'Generated Time: 9:24:28 AM
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
      set goPackage = goPackageOld

      goPackage.Name = "UpdateCAD_ProjectDemand_Mod"
      goPackage.Description = "UpdateProjectDemand"
      goPackage.WriteCompletionStatusToNTEventLog = False
      goPackage.FailOnError = False
      goPackage.PackagePriorityClass = 2
      goPackage.MaxConcurrentSteps = 1
      goPackage.LineageOptions = 0
      goPackage.UseTransaction = True
      goPackage.TransactionIsolationLevel = 4096
      goPackage.AutoCommitTransaction = True
      goPackage.RepositoryMetadataOptions = 0
      goPackage.UseOLEDBServiceComponents = True
      goPackage.LogToSQLServer = False
      goPackage.LogServerName = "(local)"
      goPackage.LogServerFlags = 256
      goPackage.FailPackageOnLogFailure = False
      goPackage.ExplicitGlobalVariables = False
      goPackage.PackageType = 0
      

'---------------------------------------------------------------------------
' begin to write package global variables information
'---------------------------------------------------------------------------

      Dim oGlobal As DTS.GlobalVariable

      Set oGlobal = goPackage.GlobalVariables.New("gv_FileLocation")
      oGlobal = "\\Seasc-Shared\Spln\Shared\Forecast\00000-000\"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("gv_ArchiveLocation")
      oGlobal = "\\Seasc-Shared\Spln\Shared\Forecast\ArchiveLocation\"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("gv_FileFullName")
      oGlobal = "00000-000.txt"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("gv_FileCheckErrors")
      oGlobal = "<not displayable>"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("gv_pathname")
      oGlobal = " "

      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing

      Set oGlobal = goPackage.GlobalVariables.New("gv_projectnum")
      oGlobal = "test2.txt"
      goPackage.GlobalVariables.Add oGlobal
      set oGlobal = Nothing


'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("DTSFlatFile")

      oConnection.ConnectionProperties("Data Source") = "c:\temp\test1.txt"
      oConnection.ConnectionProperties("Mode") = 1
      oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
      oConnection.ConnectionProperties("File Format") = 1
      oConnection.ConnectionProperties("Column Delimiter") = ","
      oConnection.ConnectionProperties("File Type") = 1
      oConnection.ConnectionProperties("Skip Rows") = 0
      oConnection.ConnectionProperties("Text Qualifier") = """"
      oConnection.ConnectionProperties("First Row Column Name") = False
      oConnection.ConnectionProperties("Number of Column") = 0
      oConnection.ConnectionProperties("Max characters per delimited column") = 255
      
      oConnection.Name = "Text File (Source)"
      oConnection.ID = 2
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = "c:\temp\test1.txt"
      oConnection.ConnectionTimeout = 60
      oConnection.UseTrustedConnection = False
      oConnection.UseDSL = False
      
      'If you have a password for this connection, please uncomment and add your password below.
      'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

      oConnection.ConnectionProperties("Integrated Security") = "SSPI"
      oConnection.ConnectionProperties("Persist Security Info") = True
      oConnection.ConnectionProperties("Initial Catalog") = "master"
      oConnection.ConnectionProperties("Data Source") = "(local)"
      oConnection.ConnectionProperties("Application Name") = "DTS Designer"
      
      oConnection.Name = "SQLServerDestination"
      oConnection.ID = 1
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = "(local)"
      oConnection.ConnectionTimeout = 60
      oConnection.Catalog = "master"
      oConnection.UseTrustedConnection = True
      oConnection.UseDSL = False
      
      'If you have a password for this connection, please uncomment and add your password below.
      'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("DTSFlatFile")

      oConnection.ConnectionProperties("Data Source") = "c:\temp\test2.txt"
      oConnection.ConnectionProperties("Mode") = 1
      oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
      oConnection.ConnectionProperties("File Format") = 1
      oConnection.ConnectionProperties("Column Delimiter") = ","
      oConnection.ConnectionProperties("File Type") = 1
      oConnection.ConnectionProperties("Skip Rows") = 0
      oConnection.ConnectionProperties("Text Qualifier") = """"
      oConnection.ConnectionProperties("First Row Column Name") = False
      oConnection.ConnectionProperties("Number of Column") = 0
      oConnection.ConnectionProperties("Max characters per delimited column") = 255
      
      oConnection.Name = "CADFileDATA.txt"
      oConnection.ID = 3
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = "c:\temp\test2.txt"
      oConnection.ConnectionTimeout = 60
      oConnection.UseTrustedConnection = False
      oConnection.UseDSL = False
      
      'If you have a password for this connection, please uncomment and add your password below.
      'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_1"
      oStep.Description = "Get first File"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSActiveScriptTask_3"
      oStep.Description = "Begin Loop"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSActiveScriptTask_3"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSDataPumpTask_1"
      oStep.Description = "CADFileDATA"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = True
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = True
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSActiveScriptTask_4"
      oStep.Description = "Loop Around"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSActiveScriptTask_4"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSActiveScriptTask_5"
      oStep.Description = "Finished"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSActiveScriptTask_5"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_2"
      oStep.Description = "Execute CADFilePath_sp"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_2"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_3"
      oStep.Description = "Get Next File"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_3"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

      oStep.Name = "DTSStep_DTSExecuteSQLTask_4"
      oStep.Description = "Set Processed Flag on Last file"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "DTSTask_DTSExecuteSQLTask_4"
      oStep.CommitSuccess = False
      oStep.RollbackFailure = False
      oStep.ScriptLanguage = "VBScript"
      oStep.AddGlobalVariables = True
      oStep.RelativePriority = 3
      oStep.CloseConnection = False
      oStep.ExecuteInMainThread = False
      oStep.IsPackageDSORowset = False
      oStep.JoinTransactionIfPresent = False
      oStep.DisableStep = False
      oStep.FailPackageOnError = False
      
goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_2")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_2"
      oPrecConstraint.PrecedenceBasis = 0
      oPrecConstraint.Value = 4
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSActiveScriptTask_3")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSDataPumpTask_1")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSActiveScriptTask_3")
      oPrecConstraint.StepName = "DTSStep_DTSActiveScriptTask_3"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSActiveScriptTask_4")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_3")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_3"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSActiveScriptTask_5")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSActiveScriptTask_3")
      oPrecConstraint.StepName = "DTSStep_DTSActiveScriptTask_3"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_3")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSExecuteSQLTask_4")
      oPrecConstraint.StepName = "DTSStep_DTSExecuteSQLTask_4"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_4")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("DTSStep_DTSDataPumpTask_1")
      oPrecConstraint.StepName = "DTSStep_DTSDataPumpTask_1"
      oPrecConstraint.PrecedenceBasis = 1
      oPrecConstraint.Value = 0
      
oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task DTSTask_DTSExecuteSQLTask_1 (Get first File)
Call Task_Sub1( goPackage      )

'------------- call Task_Sub2 for task DTSTask_DTSActiveScriptTask_3 (Begin Loop)
Call Task_Sub2( goPackage      )

'------------- call Task_Sub3 for task DTSTask_DTSDataPumpTask_1 (CADFileDATA)
Call Task_Sub3( goPackage      )

'------------- call Task_Sub4 for task DTSTask_DTSActiveScriptTask_4 (Loop Around)
Call Task_Sub4( goPackage      )

'------------- call Task_Sub5 for task DTSTask_DTSActiveScriptTask_5 (Finished)
Call Task_Sub5( goPackage      )

'------------- call Task_Sub6 for task DTSTask_DTSExecuteSQLTask_2 (Execute CADFilePath_sp)
Call Task_Sub6( goPackage      )

'------------- call Task_Sub7 for task DTSTask_DTSExecuteSQLTask_3 (Get Next File)
Call Task_Sub7( goPackage      )

'------------- call Task_Sub8 for task DTSTask_DTSExecuteSQLTask_4 (Set Processed Flag on Last file)
Call Task_Sub8( goPackage      )

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'------------- define Task_Sub1 for task DTSTask_DTSExecuteSQLTask_1 (Get first File)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask

      oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1"
      oCustomTask1.Description = "Get first File"
      oCustomTask1.SQLStatement = "SELECT TOP 1 pathname, projectnum" & vbCrLf
      oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "   FROM CADFilepath" & vbCrLf
      oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "   Where Processed = 0" & vbCrLf
      oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "   Order by projectnum"
      oCustomTask1.ConnectionID = 1
      oCustomTask1.CommandTimeout = 0
      oCustomTask1.OutputGlobalVariableNames = """gv_pathname"";""gv_projectnum"""
      oCustomTask1.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub2 for task DTSTask_DTSActiveScriptTask_3 (Begin Loop)
Public Sub Task_Sub2(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask2 As DTS.ActiveScriptTask
Set oTask = goPackage.Tasks.New("DTSActiveScriptTask")
Set oCustomTask2 = oTask.CustomTask

      oCustomTask2.Name = "DTSTask_DTSActiveScriptTask_3"
      oCustomTask2.Description = "Begin Loop"
      oCustomTask2.ActiveXScript = "' 246 (Begin Loop)" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "Option Explicit" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "Function Main()" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      dim pkg" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      dim  conTextFile " & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      dim stpEnterLoop" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      dim stpFinished" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      set pkg = DTSGlobalVariables.Parent" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      set stpEnterLoop = pkg.Steps(""DTSStep_DTSDataPumpTask_1"")" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      set stpFinished = pkg.Steps(""DTSStep_DTSActiveScriptTask_5"")" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      set conTextFile = pkg.Connections(""CADFileDATA.txt"")" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      ' We want to continue with the loop only of there are more" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      ' than 1 text file in the directory.  If the function ShouldILoop" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      ' returns true then we disable the step that takes us out of the package" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      ' and continue processing" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      if DTSGlobalVariables(""gv_pathname"").Value <> """" or not isempty(DTSGlobalVariables(""gv_pathname"").Value)  then" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpEnterLoop.DisableStep = False" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpFinished.DisableStep = True" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            conTextFile.DataSource = DTSGlobalVariables(""gv_pathname"").Value" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      else" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpEnterLoop.DisableStep =True" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpFinished.DisableStep = False" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "            stpFinished.ExecutionStatus = DTSStepExecStat_Waiting" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      End if" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "      Main = DTSTaskExecResult_Success" & vbCrLf
      oCustomTask2.ActiveXScript = oCustomTask2.ActiveXScript & "End Function"
      oCustomTask2.FunctionName = "Main"
      oCustomTask2.ScriptLanguage = "VBScript"
      oCustomTask2.AddGlobalVariables = True
      
goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub3 for task DTSTask_DTSDataPumpTask_1 (CADFileDATA)
Public Sub Task_Sub3(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask3 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask3 = oTask.CustomTask

      oCustomTask3.Name = "DTSTask_DTSDataPumpTask_1"
      oCustomTask3.Description = "CADFileDATA"
      oCustomTask3.SourceConnectionID = 3
      oCustomTask3.SourceObjectName = "\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt"
      oCustomTask3.DestinationConnectionID = 1
      oCustomTask3.DestinationObjectName = "[master].[dbo].[ProjectDemand]"
      oCustomTask3.ProgressRowCount = 1000
      oCustomTask3.MaximumErrorCount = 0
      oCustomTask3.FetchBufferSize = 1
      oCustomTask3.UseFastLoad = True
      oCustomTask3.InsertCommitSize = 0
      oCustomTask3.ExceptionFileColumnDelimiter = "|"
      oCustomTask3.ExceptionFileRowDelimiter = vbCrLf
      oCustomTask3.AllowIdentityInserts = False
      oCustomTask3.FirstRow = "0"
      oCustomTask3.LastRow = "0"
      oCustomTask3.FastLoadOptions = 2
      oCustomTask3.ExceptionFileOptions = 1
      oCustomTask3.DataPumpOptions = 0
      
Call oCustomTask3_Trans_Sub1( oCustomTask3      )
      '------- A Lookup is defined here

      Set oLookup = oCustomTask3.Lookups.New("CADFileProjNum")
            oLookup.Name = "CADFileProjNum"
            oLookup.ConnectionID = 1
            oLookup.Query = "SELECT     ProjNum" & vbCrLf
            oLookup.Query = oLookup.Query & "FROM         CADFilePath"
            oLookup.MaxCacheRows = 0
            
      oCustomTask3.Lookups.Add oLookup
      Set oLookup = Nothing
            
            
goPackage.Tasks.Add oTask
Set oCustomTask3 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask3_Trans_Sub1(ByVal oCustomTask3 As Object)

      Dim oTransformation As DTS.Transformation2
      Dim oTransProps as DTS.Properties
      Dim oColumn As DTS.Column
      Set oTransformation = oCustomTask3.Transformations.New("DTSPump.DataPumpTransformScript")
            oTransformation.Name = "DTSTransformation__1"
            oTransformation.TransformFlags = 63
            oTransformation.ForceSourceBlobsBuffered = 0
            oTransformation.ForceBlobsInMemory = False
            oTransformation.InMemoryBlobSize = 1048576
            oTransformation.TransformPhases = 4
            
            Set oColumn = oTransformation.SourceColumns.New("Col001" , 1)
                  oColumn.Name = "Col001"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 32
                  oColumn.Size = 255
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = True
                  
            oTransformation.SourceColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("textfield1" , 1)
                  oColumn.Name = "textfield1"
                  oColumn.Ordinal = 1
                  oColumn.Flags = 104
                  oColumn.Size = 1000
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = True
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

            Set oColumn = oTransformation.DestinationColumns.New("projectnum" , 2)
                  oColumn.Name = "projectnum"
                  oColumn.Ordinal = 2
                  oColumn.Flags = 104
                  oColumn.Size = 50
                  oColumn.DataType = 129
                  oColumn.Precision = 0
                  oColumn.NumericScale = 0
                  oColumn.Nullable = True
                  
            oTransformation.DestinationColumns.Add oColumn
            Set oColumn = Nothing

      Set oTransProps = oTransformation.TransformServerProperties

            oTransProps("Text") = "'**********************************************************************" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "'  Visual Basic Transformation Script" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "'************************************************************************" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "'  Copy each source column to the destination column" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "Function Main()" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "      DTSDestination(""projectnum"") = DTSGlobalVariables(""gv_projectnum"").Value" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "      DTSDestination(""textfield1"") = DTSSource(""Col001"")" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "      Main = DTSTransformStat_OK" & vbCrLf
            oTransProps("Text") = oTransProps("Text") & "End Function"
            oTransProps("Language") = "VBScript"
            oTransProps("FunctionEntry") = "Main"
            
      Set oTransProps = Nothing

      oCustomTask3.Transformations.Add oTransformation
      Set oTransformation = Nothing

End Sub

'------------- define Task_Sub4 for task DTSTask_DTSActiveScriptTask_4 (Loop Around)
Public Sub Task_Sub4(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask4 As DTS.ActiveScriptTask
Set oTask = goPackage.Tasks.New("DTSActiveScriptTask")
Set oCustomTask4 = oTask.CustomTask

      oCustomTask4.Name = "DTSTask_DTSActiveScriptTask_4"
      oCustomTask4.Description = "Loop Around"
      oCustomTask4.ActiveXScript = "' 246 (Loop Around)" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "Option Explicit" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "Function Main()" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      dim pkg" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      dim stpbegin" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      dim fil" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      dim fold" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      set pkg = DTSGlobalVariables.Parent" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      set stpbegin = pkg.Steps(""DTSStep_DTSActiveScriptTask_3"")" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      " & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      " & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      'The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      stpbegin.ExecutionStatus = DTSStepExecStat_Waiting" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "      Main = DTSTaskExecResult_Success" & vbCrLf
      oCustomTask4.ActiveXScript = oCustomTask4.ActiveXScript & "End Function"
      oCustomTask4.FunctionName = "Main"
      oCustomTask4.ScriptLanguage = "VBScript"
      oCustomTask4.AddGlobalVariables = True
      
goPackage.Tasks.Add oTask
Set oCustomTask4 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub5 for task DTSTask_DTSActiveScriptTask_5 (Finished)
Public Sub Task_Sub5(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask5 As DTS.ActiveScriptTask
Set oTask = goPackage.Tasks.New("DTSActiveScriptTask")
Set oCustomTask5 = oTask.CustomTask

      oCustomTask5.Name = "DTSTask_DTSActiveScriptTask_5"
      oCustomTask5.Description = "Finished"
      oCustomTask5.ActiveXScript = "' 246 (Finished)" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "Option Explicit" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "Function Main()" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "'uncomment to have the message display" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "      'MSGBOX ""Package has Completed.""" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "      Main = DTSTaskExecResult_Success" & vbCrLf
      oCustomTask5.ActiveXScript = oCustomTask5.ActiveXScript & "End Function"
      oCustomTask5.FunctionName = "Main"
      oCustomTask5.ScriptLanguage = "VBScript"
      oCustomTask5.AddGlobalVariables = True
      
goPackage.Tasks.Add oTask
Set oCustomTask5 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub6 for task DTSTask_DTSExecuteSQLTask_2 (Execute CADFilePath_sp)
Public Sub Task_Sub6(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask6 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask6 = oTask.CustomTask

      oCustomTask6.Name = "DTSTask_DTSExecuteSQLTask_2"
      oCustomTask6.Description = "Execute CADFilePath_sp"
      oCustomTask6.SQLStatement = "--Executes the proc that gets the files to load" & vbCrLf
      oCustomTask6.SQLStatement = oCustomTask6.SQLStatement & "exec CADFilePath_sp"
      oCustomTask6.ConnectionID = 1
      oCustomTask6.CommandTimeout = 0
      oCustomTask6.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask6 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub7 for task DTSTask_DTSExecuteSQLTask_3 (Get Next File)
Public Sub Task_Sub7(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask7 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask7 = oTask.CustomTask

      oCustomTask7.Name = "DTSTask_DTSExecuteSQLTask_3"
      oCustomTask7.Description = "Get Next File"
      oCustomTask7.SQLStatement = "SELECT TOP 1 pathname, projectnum" & vbCrLf
      oCustomTask7.SQLStatement = oCustomTask7.SQLStatement & "   FROM CADFilepath" & vbCrLf
      oCustomTask7.SQLStatement = oCustomTask7.SQLStatement & "   Where Processed = 0" & vbCrLf
      oCustomTask7.SQLStatement = oCustomTask7.SQLStatement & "   Order by projectnum"
      oCustomTask7.ConnectionID = 1
      oCustomTask7.CommandTimeout = 0
      oCustomTask7.OutputGlobalVariableNames = """gv_pathname"";""gv_projectnum"""
      oCustomTask7.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask7 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub8 for task DTSTask_DTSExecuteSQLTask_4 (Set Processed Flag on Last file)
Public Sub Task_Sub8(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask8 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask8 = oTask.CustomTask

      oCustomTask8.Name = "DTSTask_DTSExecuteSQLTask_4"
      oCustomTask8.Description = "Set Processed Flag on Last file"
      oCustomTask8.SQLStatement = "UPDATE CADFilepath" & vbCrLf
      oCustomTask8.SQLStatement = oCustomTask8.SQLStatement & "   Set Processed = 1   " & vbCrLf
      oCustomTask8.SQLStatement = oCustomTask8.SQLStatement & "   WHERE projectnum = LTRIM(RTRIM(?))"
      oCustomTask8.ConnectionID = 1
      oCustomTask8.CommandTimeout = 0
      oCustomTask8.InputGlobalVariableNames = """gv_projectnum"""
      oCustomTask8.OutputAsRecordset = False
      
goPackage.Tasks.Add oTask
Set oCustomTask8 = Nothing
Set oTask = Nothing

End Sub


0
 

Author Comment

by:Karen Schaefer
ID: 12727473
Thanks to adwiseman for all his hardwork - you are amazing to take so much time and effort to assistant me.  Sorry I did not get back to you til now- meetings you know!

All your suggestions helped me to understand the process it needed to take to accomplish my goal.

THANKS!
Karen
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.
Suggested Courses

862 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