DTS Default Values + XLS filenames

[1DMF]

Hi,

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!

 

[drlex]

As to 1., could you not do an UPDATE on the table after the data has been inserted from Excel?
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é

 

[1DMF]

1. but if i'm importing multiple CSV files, how do I know what records to update and what ones not to and with what.

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!

 

[mutley1]

There are various ways to fill in the blanks in Q1, but need a bit more info. If, for example field 3 is blank, do you want it to be populated with something static, or will it vary on record type / another field? If it is empty in the spreadsheet / csv, and you want it to always say XYZ, you can just set a default on the column.

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.

 

[1DMF]

I apprecaiate your input Matt.

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!