DTS Import txt File looping.

Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>