Hi,
I have one requirement something like this. I have 13 dat files at one location. Need to copy the data from those 13 files into one SQL Server table at one time. I am planning to use DTS. But how to configure DTS package to get the data from those 13 files at a time and insert into one SQL Server Table. Pls throw your lights on this.
thx
Bachi

Well if they all look the same then this will do it for you

http://support.microsoft.com/?kbid=269074

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Bachi" <anonymous (AT) discussions (DOT) microsoft.com> wrote

table at one time. I am planning to use DTS. But how to configure DTS
package to get the data from those 13 files at a time and insert into one
SQL Server Table. Pls throw your lights on this.

Hi,
They all with different file names in the same folder. But I did not get anything from above reply. Could you pls elobrate on this. my Question is : Actually i can give one text file as source of DTS package.Is there any way to supply multiple files as Source of DTS package ?
thx
Bachi

Ever so sorry I posted the wrong article.

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"bachi" <anonymous (AT) discussions (DOT) microsoft.com> wrote

Actually i can give one text file as source of DTS package.Is there any way
to supply multiple files as Source of DTS package ?

Hi There,
I have faced a similar issue and also have been to SQLDTS
(excelent!)
But I have made a few modifications.
1. I browse through the source directory and place the
name of the files to be imported in temporary Global
Variables.

'************************************************* *********
************
' Visual Basic ActiveX Script
' Populate Array
'************************************************* *********
**************
Function Main()
Set oPKG = DTSGlobalVariables.Parent
Set fso = CreateObject
("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(DTSGlobalVariables
("PastaOrigem").Value)
nFicheiros=1
For Each fsoFile in fsoFolder.Files

oPkg.GlobalVariables.AddGlobalVariable "cFicheiro"&Trim
(cStr(nFicheiros)), fsoFolder.Path &"\"& fsoFile.Name
nFicheiros=nFicheiros+1
Next
oPkg.GlobalVariables.AddGlobalVariable "nFicheiros
", nFicheiros-1
Set oConnection = oPKG.Connections("Text File
(Source)")
oConnection.DataSource=DTSGlobalVariables
("cFicheiro"&Trim(cStr(nFicheiros-1))).Value
Main = DTSTaskExecResult_Success
End Function

2. Then, I Browse through the array of global variables,
changing the data source.

'************************************************* *********
************
' Visual Basic ActiveX Script
' Next
'************************************************* *********
**************
Function Main()
DTSGlobalVariables("nFicheiros")=DTSGlobalVariable s
("nFicheiros").Value-1
If DTSGlobalVariables("nFicheiros") > 0 Then
Set oPKG = DTSGlobalVariables.Parent
Set oConnection = oPKG.Connections("Text
File (Source)")
oConnection.DataSource = DTSGlobalVariables
("cFicheiro"&Trim(cStr(DTSGlobalVariables("nFichei ros"))))
oPKG.Steps
("DTSStep_DTSDataPumpTask_1").ExecutionStatus =
DTSStepExecStat_Waiting
End if
Main = DTSTaskExecResult_Success
End Function


Hope this helps...
Valmir Meneses
MCDBA, MCAD

HI
Thanks for your help. I am able to complete my taks with ur help
once again thanks
bach

Hi There
I have faced a similar issue and also have been to SQLDTS
(excelent!
But I have made a few modifications
1. I browse through the source directory and place the
name of the files to be imported in temporary Global
Variables

'************************************************* ********
***********
' Visual Basic ActiveX Scrip
' Populate Arra
'************************************************* ********
*************
Function Main(
Set oPKG = DTSGlobalVariables.Paren
Set fso = CreateObjec
("Scripting.FileSystemObject"
Set fsoFolder = fso.GetFolder(DTSGlobalVariable
("PastaOrigem").Value
nFicheiros=
For Each fsoFile in fsoFolder.File

oPkg.GlobalVariables.AddGlobalVariable "cFicheiro"&Tri
(cStr(nFicheiros)), fsoFolder.Path &"\"& fsoFile.Nam
nFicheiros=nFicheiros+
Nex
oPkg.GlobalVariables.AddGlobalVariable "nFicheiro
", nFicheiros-
Set oConnection = oPKG.Connections("Text File
(Source)"
oConnection.DataSource=DTSGlobalVariable
("cFicheiro"&Trim(cStr(nFicheiros-1))).Valu
Main = DTSTaskExecResult_Succes
End Functio

2. Then, I Browse through the array of global variables,
changing the data source

'************************************************* ********
***********
' Visual Basic ActiveX Scrip
' Nex
'************************************************* ********
*************
Function Main(
DTSGlobalVariables("nFicheiros")=DTSGlobalVariable
("nFicheiros").Value-
If DTSGlobalVariables("nFicheiros") > 0 The
Set oPKG = DTSGlobalVariables.Paren
Set oConnection = oPKG.Connections("Text
File (Source)"
oConnection.DataSource = DTSGlobalVariable
("cFicheiro"&Trim(cStr(DTSGlobalVariables("nFichei ros")))
oPKG.Step
("DTSStep_DTSDataPumpTask_1").ExecutionStatus =
DTSStepExecStat_Waitin
End i
Main = DTSTaskExecResult_Succes
End Function

Hope this helps..
Valmir Menese
MCDBA, MCAD