| Author |
Topic |
|
bnhcomputing
Starting Member
22 Posts |
 Posted - 2005-10-07 : 15:09:01
|
| I have a DTS that imports data from Excel into SQL. It only works if the file name and location are exactly the same every time. I tried using the Dynamic Properties Task, and I tried changing the 'connection.datasource' via an active X script.It doesn't work either way. Using the same data file EVERY time, just changing the name the package fails.The Microsoft Jet database engine could not find the object 'Sheet1$'If I change the physical name of the file, and then change the global variable it doesn't work.The JET engine must be storing additional filename info somewhere.Does anybody know what else I am missing or how to get this to work?Hubert Hoffman |
|
|
mriverol
Starting Member
10 Posts |
Posted - 2005-10-09 : 08:10:53
|
| Hubert,If the excel file structure is always identical I suspect subsequent excel files have differing worksheet name than sheet1. This would cause the error as the transformation is looking for a source named Sheet1 that does not exist. If you want to be able to do this the worksheet name must always be identical otherwise the saved transformations will need to be re configured.If your load files are indeed the same in structure and differ only in data and filename you can create a controlling package to change the filename and run your loading package. In your loading package create a global variable to hold the name of the load file and then add a Dynamic properties task to tie the excel connections datasource value to the global variable value. Then in your controlling package you can instantiate your loading package (LoadFromSQLServer method), update the filename global variable and execute it.Martin |
 |
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-10-09 : 11:30:44
|
| That isn't a bad idea. However, if I do that then this will become a SINGLE user task. Only able to process one file at a time. If I have 100 users, I would need 100 different DTS packages. Need to make it work correctly, not work around, but thanks for the suggestion.In answer to the other question, it is the EXACT same file, differing in name and/or location only.Any other ideas would be most helpfull.Hubert Hoffman |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-09 : 16:09:33
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926 |
|
|
|
mriverol
Starting Member
10 Posts |
Posted - 2005-10-10 : 07:11:19
|
| Hi Hubert,I am a little confused. This approach will not tie you to a single user, you can instantiate as many controlling package objects as you wish and they can run concurrently. Remember, all you are doing is assigning a source file value to the child package prior to running it.Martin. |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-10-10 : 10:04:10
|
| Martin:As I understand you suggestion, you would like me to use active-X to "copy/rename" the source file to lets say "D:\INPUT\SOURCE1.XLS" just prior to doing the import.The issue I see is that if there are twenty users, then there is the potential to overwrite/change the data in SOURCE1.XLS. ie (user7 package overwrites user2 data)The link from rockmoose starts out talking about "exporting", my problem is changing the name/location on import.So lets address this simpliticaly.1. Does DTS Dynamic Properties Task "work" with MS Excel 2000 to change the input file (YES/NO).2. If it does, then why can I get it to work?3. Changing the datasource name for output to EXCEL DOES WORK, why doesn't the same code work for changing the import file name.4. As stated previously, the file is EXACTLY the same. Only the name and location have changed. (D:\INPUT\SOURCE1.XLS) When the file is there, the DTS works. I copy source1.xls to "D:\INPUT\SOURCE2.XLS". Change the datasource name via Active-X or the "Dynamic Properties Task" to "D:\INPUT\SOURCE2.XLS", and the DTS works. Delete D:\INPUT\SOURCE1.XLS. DTS FAILS.There was an example in the post from rockmoose that used a select and I will try to implement that. However, if this in the ONLY way to get this to work, then the standard DTS transform data task, when changing the Excel input file, does not work.Please advise----------HubertHubert Hoffman |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-10 : 13:20:28
|
I just posted the link since I thought it might be of use.(DTS, and xls is not something I usually fiddle with  )you might search:www.sqldts.comIf you don't get any further help. |
|
|
|
mriverol
Starting Member
10 Posts |
Posted - 2005-10-11 : 07:15:08
|
| Hi Hubert,no, I don't want you to rename the source file. I want you to rename the source connection to point at another file identical in structure. Follow the steps below and you should see this is possible.1. Make a copy the file that you wish to load as 'copy of ...'. This will be the filename that we are going to substitute our original filename for.2. Create a package containing your connections to original excel document and sql server then put in transformation between the two sources.3. Delete or move original file.4. Go into disconnected edit -> Excel connection -> DataSource and amend the source name to file 'Copy of...'5. Try to run load package and your done.You will need the active-x script in a parent package to load up the dts object and change the global variable that holds the file name and path. The Dynamic properties task will update the Excel connection -> DataSource based on the value you have put into this global variable. Martin. |
|
|
|
bnhcomputing
Starting Member
22 Posts |
Posted - 2005-10-11 : 11:33:13
|
| Martin:Sorry for sounding so stupid. I GOT IT WORKING!You were right on the money. Created a "Master" DTS package. It sets up everything and then does an Execute Package Task.Not only did I get this to work with the Excel file name/location being different, I was also able learn/change the name of the excel sheet, on the fly, as well.I now have a fairly smart DTS package.Thanks to everyone for all your help.------HubertHubert Hoffman |
|
|
|
|
DTS Excel --> SQL Error