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 |
|
murrayb3024
Yak Posting Veteran
79 Posts |
 Posted - 2005-03-23 : 15:37:06
|
| I have been working on a DTS package that I need to import Excel Documents to a database. I ran into a problem in that the user wants to import 7 excel docuemnts on Friday, one for each day of the week. So they will put 7 in a specfied directory, naming them Monday.xls, Tuesday.xls, etc. I have gotten some help and built a ActiveX script that is suppose to change the name of the file in the file location on the Excel Data Source. Here is the code that was developed: Function Main() Dim oConn, sFilename, i DTSGlobalVariables("FilePath").value = "\\cypress\groups\timecards\" ' Filename format - exyymmdd.log For i = 1 to 7 DTSGlobalVariables("FileDay").Value = WeekDayName(i) sFilename = DTSGlobalVariables("FilePath").Value & _DTSGlobalVariables("FileDay").Value & ".xls" Set oConn = DTSGlobalVariables.Parent.Connections("File (Source)") oConn.DataSource = sFilename Set oConn = Nothing Main = DTSTaskExecResult_Success Next End Function The problem I am now having is that the script runs through all 7 days finsished on Saturday, sets the file location with Saturday.xls and then does the import. So basically I am only getting the Saturday xls to import. Any ideas on why it is not exiting out on each day, or what I can do to fix this? |
|
|
PW
Yak Posting Veteran
95 Posts |
Posted - 2005-03-23 : 16:42:37
|
| This entire script runs *before* any data transformation, therefore the transformation will always use the result of the *last* iteration of the loop. I suggest you visit www.sqldts.com and read the examples on how to implement looping in a package:http://www.sqldts.com/default.aspx?246 |
 |
|
|
|
|
|
DTS Package problem