Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chowchow123
Starting Member
3 Posts |
 Posted - 2008-11-25 : 04:48:50
|
| HiI have the following in a dts package which picks an xml file and reads it into the database. It does this successfully when the path and filename is specified, but I want the xml file to be read dynamically and so I've used objFSOFolder & objFSOFile.name below (highlighted the line with '********** Problems reading file *****) The script executes but I get 'error opening the data file' how could I rectify this Function Main() Set objFSO = CreateObject("Scripting.FileSystemObject") strInboxDir = "C:\Inbox\" strArchiveDir = "C:\Inbox\Archive\" Set objFSOFolder = objfso.getfolder(strInboxDir) for each objFSOFile in objfsofolder.files if (left(objFSOFile.name, 7) ="NewFile" & right(objFSOFile.name,3) = "xml") then strFileNameBase = trim(right(objFSOFile.Name,len(objFSOFile.name)+7)) & trim(left(objFSOFile.Name, len(objFSOFile.name)-4)) master.WriteLine objFSOFile.name & " in folder @ " & now end if Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload") objBulkLoad.ConnectionString = "provider=SQLOLEDB;data source=localhost;initial catalog=myDB; integrated security=SSPI" objBulkLoad.ErrorLogFile = "C:\Inbox\Errorlog.txt" objBulkLoad.Execute "C:\Schemas\Schema1.xsd", objFSOFolder & objFSOFile.name '******** Problems reading file ******* Set objBulkLoad = Nothing Main = DTSTaskExecResult_Success next Set fso = CreateObject("Scripting.FileSystemObject")'' Move File to the Archive Directory' If not fso.FileExists(strArchiveDir & "\NewFile" & strFileNameBase & ".xml") Then fso.copyfile strInboxDir & "\NewFile" & strFileNameBase & ".xml", strArchiveDir & "\NewFile" & strFileNameBase & ".xml",true master.WriteLine "" & strFileNameBase & ".xml was moved to the Archive Folder @" & now Main = DTSTaskExecResult_Success Else fso.DeleteFile strInboxDir & "\NewFile" & trim(strFileNameBase) & ".xml" master.WriteLine " NewFile" & strFileNameBase & ".xml already exists and was Deleted @" & now Main = DTSTaskExecResult_Success END IF End Function |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-11-25 : 07:11:56
|
You can use a messagebox to display the result of your work on the objFSOFile.name (with trim and left and right...).Maybe it is not the expected result...Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
help with script