November 24, 2004 at 2:25 pm
I am trying to set Global Variable that will open a txt file and import the data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad Directory error.
My filename data is stored in another table - I am looking for the best approach to calling the filename from the table = cadfilepath and opening the actual text file and importing the data into my project table.
ie.
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.
I know this sounds confusing and I can email you my documentation - since this website does not allow for attachments.
Any assistances is greatly appreciated.
Karen
November 29, 2004 at 8:00 am
This was removed by the editor as SPAM
November 29, 2004 at 1:36 pm
I am not sure what you are doing. However, since you mentioned what you want to do allow me to give you an idea that could work for you if you are using SQL Server 2000.
You can keep using the global variable to store the correct path and filename. Now use a Dynamic Properties Task to set the source connection's path and filename to the value in your global variable. Make sure the data pump between the source and destination tasks does not run until about 2 or 3 seconds after the Dynamic Properties Task and then you should have no problems.
This solution assumes that the data in the text file is stored in the same sequence with the same number of columns each time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 29, 2004 at 1:45 pm
Sorry if I confused you. What I am trying to accomplish is the following:
1. Determine a list of files(.txt) that need to be used to import data. (I already have this portion complete within a DTS Package)
2. Loop thru this list importing the data 1 recordset at a time or all at once if possible.
3. Update a table using the importing data, plus adding a field it include the projectNumber(this is not data contained within the file, however, it is the actual filename), Datecreated, UserID.
Loop to the next record(filename) until EOF.
If there is a better way of importing data based on list of files, feel free to make suggestions.
Could you be more specific - give examples please.
Thanks,
Karen
November 29, 2004 at 2:15 pm
Whenever I use text files I import them into a table before trying to manipulate them. You could use a table as an intermediate table before inserting into the final table.
If you can set up the entire process for one of your files then another way is to use what I suggested above to change the file each time and use an ActiveX Script task with the following code to make a task and all tasks that come after it execute again:
Dim oPkg
Set oPkg = DTSGlobalVariables.Parent
'Set step called ExecuteSQLTask_FetchNextStaffSectionID to waiting so that it and all subsequent steps will run again.
oPkg.Steps("ExecuteSQLTask_FetchNextStaffSectionID").ExecutionStatus = DTSStepExecStat_Waiting
Simply replace ExecuteSQLTask_FetchNextStaffSectionID with the name of the step you want to have execute again.
Then you set up a method that increments through the file names one by one and a way to have the above code stop executing once the last file is processed and after that you could import all that data into the final table.
Hopefully I am not too far off from what you want to do.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
November 29, 2004 at 3:44 pm
I have not used the Dynamic Property Task - could you assist me an what I would need to input
Do you know why when I use the SQLDTS.com "Looping, Importing & Archiving" Code why it fails do to "Bad Directory"
Karen
December 1, 2004 at 6:01 am
Karen
Have just implemented something similar to what you are trying to achieve, please let me know if you still need assistance.
Russ
December 1, 2004 at 9:23 am
Yes thank you
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply