DTS Default Values + XLS filenames
DTS Default Values + XLS filenames
I've managed to create a DTS package, first one ever !!!!
However I have a few querries..
1. How do i get the DTS package to supply default data that isn't in the source file XLS, but i want adding on insert to the destination SQL table.
2. When creating the DTS package I had to physically locate the XLS spreadsheet, what if the name changes all the time, how do I tell it to pick up any XLS file in a specific folder?
Thanks,
1DMF.
"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
RE: DTS Default Values + XLS filenames
As to 2, you'd need to loop through a folder's contents - have a look at this page - http://www.sqldts.com/246.aspx
soi la, soi carré
RE: DTS Default Values + XLS filenames
what I got from your reply was 'You cannot do that', are you really saying DTS doesn't allow for the missing data to be pre-populated with the DTS package.
Why do I keep getting the feeling MS come up with a great idea and then their implementation sucks!
2. I don't think i'll bother, it's quicker and easier to write an import facility with VBA in Access and use the transferspreadsheet method.
Thanks anyway.
"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
RE: DTS Default Values + XLS filenames
If it varies from record to record (i.e. if field 1=A then XYZ, if field 1=B then QRS etc. then you could do it sith a select into statement instead of "the whole import file" with a case satement.
Q2. I have a script that pulls back files in from a directory to a table and processes them but it is not with me at the moment. I will try to bring it in tomorrow if you want it.
Thx,
Matt.
RE: DTS Default Values + XLS filenames
However, I have already written my own mapping tool / import facility, only took an afternoon and then a few bits of tweaking.
Now I can simply have an XLS(CSV) file from any provider, delete the content from all rows, then under each column put the names of the fields to map across.
I then use my tool to select it and save against a specific provider.
When we have a new data file to import, you now select the XLS and the provider the XLS is for and click import, the special CSV map does the rest.
I can then include further data manipulation as it imports to a 'holding' table, once full exception repoiting and data amendments are done, the entire commissions run can be batch processed.
Long way to go till I have a fully working application, but the mapping works fine.
"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."
"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"