DTS with variable filenames and variable columns?

Question

DTS with variable filenames and variable columns?

MS Verbsthenoun

Ten Centuries

Points: 1153
More actions
December 14, 2005 at 3:16 am

#67667

I have been banging my head against this issue for quite some time.
I'm not heavily experienced with MS SQL 2000, but have made significant progress in various parts of this project, but this last area has been stumping me.
I've tried through DTS, bcp, and Bulk Insert, and get close, but can't quite find the right combination to make this work. I hope someone here can help.
There is a directory that will be receiving "project" files in dbf (dbaseiv) format from Lotus Approach.
The filenames will always be varying in no particular pattern, the only constant will be the .dbf extension.
Additionally, there will be a variable range of columns. Some of the columns will always be there (firstname, lastname, etc.), but there will be 'extra" columns that do NOT need to be imported.
I tried using bulk insert for this, but apparently that's just for text files not dbf/binary files?
I tried using DTS, but it requires specified filenames and columns mappings (from what I can tell).
Basically, I want to be able to import c:\projectdir\*.dbf to a temp database all columns from all columns from firstname through to projnum, and ignore all the columns after the projnum column.
If it is easier to just import the entire column/data structure into a temporary table dynamically generatred during a bulk import for all columns, and then once it's on the sql server, just handle the needed columns (ignoring the "unneeded" ones) all on the server, that's fine too, I just haven't been able to get such a dynamic setup to work with the dbf format (what I found was for bulk insert (or bcp, it showed both) of a delimited text file, not binary/dbf).
So, what would be the best tool for this? If it's DTS, how the heck to I get DTS to usr c:\projectdir\*.dbf ?
And how can it import just the columns I want and ignore the rest (or dynamically create a new temporary table to then use just the desired columns to a separate temp/perm table?)?
And what would be the steps/code to make it happen?
Please let me know if I need to provide any additional information.
Thanks kindly.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michel Murry Valued Member Points: 59 More actions · Answer 1

HI I am also stuck with this problem I need to export a file using a different file name everyday eg appending the date onto the file name. I have tried to use DTS but am not having any success with a variable file name

Thanks

Michele

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 2

Variable filenames..

I move file(s) from an FTP server to a directory called "source" (Using ActiveX fso and a loop). The file name looks like:

Data20051214_950.csv

After moving all the files from FTP the next step in the DTS package (again using fso) moves one file into a directory called "work" with a common name. (the move changes the name to simply:

Data.csv

And the data pump task executes against this file. After completion the DTS loops back to move another file from "Source" 'till the directory is empty. Hope this helps.

Cheers..

John Rowan SSC Guru Points: 56440 More actions · Answer 3

jeffroyal's method for renaming the file to the source filename that your DTS task is expecting should work fine. As far as only importing certain columns, I believe that you can handle this by playing with the transformations.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

An alternative to the variable columns problem is to use more T-SQL and less DTS.

eg:

SELECT *

INTO TempTableName

FROM OpenRowSet('MSDASQL', 'Driver={ [Insert DBF driver specifics here] }'

INSERT INTO PermanentTable

(FirstName, LastName, ....)

SELECT FirstName, LastName, ....

FROM TempTableName

DROP TABLE TempTableName

Of course, with this approach, you still need the logic to change the .DBF filename inside the driver setup of the OpenRowSet().

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 5

I'd PREFER to do it all in sql rather than dts, but I can't seem to get it to correctly handle the file format.

What is the driver information/code/syntax I would need to insert for the *.dbf to import wildcarded?

I can import if I hardcode the columns and filenames, that's been working fine all along, it's the wildcarding that I can't seem to figure out.

And then how do I get it to dynamically create the correct columns, or only import the columns I want and ignore the rest? I know how I can ignore the rest of the columns once it's actually in sql server,but how to get it to that point is the issue?

Thanks.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 6

I'll try fiddling around with that then. I was hoping I could do things in a more SQL related way, but if DTS can be made to do it correctly, then I'll settle for that. Thanks!

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 7

So what is this fso you're referring to? ?File System Object?

I'm NOT an activeX developer at all. In fact, the hope is that eventually they will migrate off this ms sql approach hybrid, to a fully integrated web based workflow setup that will be much cleaner. But unfortunately, for now, this kludge of Lotus Approach, MS SQL, Excel, FTP pipe delimited text files, dbaseIV files, are what has to be worked with to clean up the data mess that is there. So, until the web based solution is underway, anything I can do in SQL, and/or MS SQL without getting into any MS dependent programming (beyond tsql and ms sql code and tools for the backend), the better.

However, in this case, with importing the dynamic filenames and dynamic columns, I'm desperate enough to try anything at this point, but it requires some pretty heavy guidance unfortunately if it steps outside of just sql code. If you can show me some code that I can work with in the dts activeX component, I can try to "hack' something together, but I need to see some sample code to work with it.

Any chance you can help with that please?

Thanks.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 8

I see in the DTS package tool, with the "File Transfer Protocol" I can choose the source to be either Internet or Directory. So I choose Directory. Then it lists the files in the left hand side, and wants me to add them to the right hand side. But see, that is'nt wildcarding, how to make the DTS package just take ANY dbf files in that directory automatically? Instead of having to pick each one each time? Different tool inside DTS package toolset?

Thanks.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 9

What should I look for in BOL for the Driver details information?

Thanks.

MS Verbsthenoun Ten Centuries Points: 1153 More actions · Answer 10

So, I can get this code to work:

SELECT *

INTO Project01tmp

FROM OpenRowSet{'MSDASQL', 'Driver=(Microsoft dBASE Driver (*.dbf(};DriverID=277;DBQ=C:\ProjectDir', 'select * from Proj01');

INSERT INTO Project01tmp

(FirstName, LastName, ....)

But again, I'm having to specify the filename. How do I wildcard it correctly to import ANY dbf file? The client has no problem only putting one file at a time in the directory, it's just that the names will vary all the time.

So, is there a way to make it work as *.dbf?

Thanks.

iBuildDW Mr or Mrs. 500 Points: 587 More actions · Answer 11

Here is an example of an activeX step you could add to your DTS package to copy one file from a directory to another directory (with a common name, in this case file.csv)

Function Main()

dim fso

dim fldr

dim fc

dim f

dim strPath

dim strSourcePath

dim strWorkPath

dim strStoreNum

' set some paths

strPath="C:\ETL\"

strSourcePath=strPath & "Source\"

strWorkPath=strPath & "Work\"

dim oPkg

set oPkg=DTSGlobalVariables.Parent

' instantiate the Scripting Object

set fso = CreateObject("Scripting.FileSystemObject")

' get folder for fldr object

set fldr=fso.GetFolder(strSourcePath)

' get file collection from fldr object

set fc=fldr.files

if fc.count>0 then ' there are files in the source directory

' clear out the Work directory

fso.DeleteFile (strWorkPath & "*.*")

' loop thru each file (actually, we're only going to get the first one and then exit the 'for each')

for each f in fc

' move file from Source directory to Work directory, with common name

fso.MoveFile strSourcePath & f.Name, strWorkPath & "file.csv"

Main = DTSTaskExecResult_Success

exit for

Main = DTSTaskExecResult_Failure ' No files to process

exit function

end if

End Function