First of all, credit goes to http://www.sqldts.com/default.aspx?246 for helping me with this idea. Basically I have several files I wish to import - files are in different format so I can really use a datapump, so I was going to use a Bulk Insert Task and change the DataFile and DestinationTableName on the fly.

Below is the start of my loop...and right off the bat I have issues

First - When I execute, a error pops up
"Task 'DTSStep_DTSExecuteSQLTask_1' was not found.
Well it is there. I changed the workflow option on that task and even copied and paste the name. I think it bombs out here
set stpEnterLoopPG1 = pkg.Steps("DTSStep_DTSExecuteSQLTask_1"

I'm sure I will have more flaws in the code below, but I need to get past line 20 for a change ;-)

Thanks for your time..
Richar

'================================================= ====================
Option Explici

Function Main(
dim pk
dim bulkTextFilePG
dim stpEnterLoopPG
dim stpFinishedPG
Dim bulkSQLTablePG
Dim SQLTaskPG

set pkg = DTSGlobalVariables.Paren
' set stpEnterLoopPG1 = pkg.Steps("DTSTask_DTSBulkInsertTask_1").CustomTas k 'This is the Bulk Insert Task..
'Instead of starting the loop on the Bulk Insert Task, start on the SQL Task - to truncate the table prior to Insert
set stpEnterLoopPG1 = pkg.Steps("DTSStep_DTSExecuteSQLTask_1") 'This is the SQL Task..
set stpFinishedPG1 = pkg.Steps("DTSStep_DTSActiveScriptTask_2"
set bulkTextFilePG1 = pkg.Tasks("DTSTask_DTSBulkInsertTask_1"
Set bulkSQLTablePG1 = pkg.Tasks("DTSTask_DTSBulkInsertTask_1"
Set SQLTaskPG1 = pkg.Tasks("DTSStep_DTSExecuteSQLTask_1"

' We want to continue with the loop only of there are mor
' than 1 file to process in the process group. If the function ShouldILoo
' returns true then we disable the step that takes us out of the packag
' and continue processin

if ShouldILoop = True the
stpEnterLoopPG1.DisableStep = Fals
stpFinishedPG1.DisableStep = Tru
'Supply the Source file for the Bulk Insert Task
bulkTextFilePG1.DataFile = DTSGlobalVariables("gvWNBDir").Value & DTSGlobalVariables("gvImportFilePG1").Valu
'Supply the Destination Table for the Bulk Insert Task
bulkSQLTablePG1.DestinationTableName = DTSGlobalVariables("gvSQLTablePG1").Valu
SQLTaskPG1.SQLStatement = "Truncate Table " & DTSGlobalVariables("gvSQLTablePG1").Valu
stpEnterLoopPG1.ExecutionStatus = DTSStepExecStat_Waitin
els
stpEnterLoopPG1.DisableStep = Tru
stpFinishedPG1.DisableStep = Fals
stpFinishedPG1.ExecutionStatus = DTSStepExecStat_Waitin
End i
Main = DTSTaskExecResult_Succes
End Functio

Function ShouldILoo
dim pk
Dim sProces

'Here read from the database to find the next file to Bulk Insert into the database
sDSN = DTSGlobalVariables("gvDSN").value '"Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=WNB;Data Source=DEVSQL01;
'ConnectToDatabas
Set oCN = CreateObject("ADODB.Connection"
oCN.Open sDS
'Get the next process..
sQry = "select top 1 process from processes where processgroup = '1' and status = 'D' order by process
Set oRS = CreateObject("ADODB.Recordset"
oRS.CursorType = 3 'adUseStatic - must be to get a record coun
oRS.open sQry, oC
'Check to see if there are records in the record set (oRS
If oRS.RecordCount > 0 The
Do Until oRS.EO
sProcess = oRS.Fields(0).Valu
oRS.MoveNex
Loo
' MsgBox sProces
set pkg = DTSGlobalVariables.Paren
'Set the Source Text file for import on the Bulk Insert Task..
DTSGlobalVariables("gvImportFilePG1").Value = sProcess & ".txt
'Set the Destination table for the Bulk Insert Task...and Set which table to pass to the SQL Task for truncate..
DTSGlobalVariables("gvSQLTablePG1").Value = sProces
ShouldILoop = CBool(True
Els
' MsgBox "Nothing
ShouldILoop = CBool(False
End I
oRS.Clos
End Functio

ok, I figured out my first question as soon as I posted it (figures). But now I have a new one.

Object doesn't support this property or method: 'bulkTextFilePG1.DataFile'

Seems to be an issue with my BulkInsert Task. Have a feeling I will have the same issue with the DestinationTableName as well. Thoughts?

Thanks again...
Richard

'================================================= ========
Option Explicit

Function Main()
dim pkg
dim bulkTextFilePG1
dim stpEnterLoopPG1
dim stpFinishedPG1
Dim bulkSQLTablePG1
Dim SQLTaskPG1
Dim TaskDataFile
Dim TaskTableName

set pkg = DTSGlobalVariables.Parent
' set stpEnterLoopPG1 = pkg.Steps("DTSTask_DTSBulkInsertTask_1").CustomTas k 'This is the Bulk Insert Task...
'Instead of starting the loop on the Bulk Insert Task, start on the SQL Task - to truncate the table prior to Insert.
set stpEnterLoopPG1 = pkg.Steps("DTSStep_DTSExecuteSQLTask_1") 'This is the SQL Task...
set stpFinishedPG1 = pkg.Steps("DTSStep_DTSActiveScriptTask_2")
set bulkTextFilePG1 = pkg.Tasks("DTSTask_DTSBulkInsertTask_1")
Set bulkSQLTablePG1 = pkg.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTas k
Set SQLTaskPG1 = pkg.Tasks("DTSTask_DTSExecuteSQLTask_1")

' We want to continue with the loop only of there are more
' than 1 file to process in the process group. If the function ShouldILoop
' returns true then we disable the step that takes us out of the package
' and continue processing

if ShouldILoop = True then
stpEnterLoopPG1.DisableStep = False
stpFinishedPG1.DisableStep = True
'Supply the Source file for the Bulk Insert Task.
TaskDataFile = DTSGlobalVariables("gvWNBDir").Value & DTSGlobalVariables("gvImportFilePG1").Value
bulkTextFilePG1.DataFile = TaskDataFile
'Supply the Destination Table for the Bulk Insert Task.
TaskTableName = DTSGlobalVariables("gvSQLTablePG1").Value
bulkSQLTablePG1.DestinationTableName = TaskTableName
SQLTaskPG1.SQLStatement = "Truncate Table " & DTSGlobalVariables("gvSQLTablePG1").Value
stpEnterLoopPG1.ExecutionStatus = DTSStepExecStat_Waiting
else
stpEnterLoopPG1.DisableStep = True
stpFinishedPG1.DisableStep = False
stpFinishedPG1.ExecutionStatus = DTSStepExecStat_Waiting
End if
Main = DTSTaskExecResult_Success
End Function

For the ExecuteSQL task the SQLStatement is assigned to the CustomTask
property not the object itself.
The DataFile property is of the CustomTask for the BulkExport task not the
task itself

This is wrong

Set SQLTaskPG1 = pkg.Tasks("DTSStep_DTSExecuteSQLTask_1")

You are asking for a task and passing the name of a Step





--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Richard Ferrara" <richard (AT) theferraras (DOT) net> wrote

import - files are in different format so I can really use a datapump, so I
was going to use a Bulk Insert Task and change the DataFile and
DestinationTableName on the fly.
Task...

DOH! Of course! And it was right there in front of me. Changed to pkg.Steps and all is well

Thanks for your time..
Richard