×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

DTS Default Values + XLS filenames

DTS Default Values + XLS filenames

DTS Default Values + XLS filenames

(OP)
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!"

RE: DTS Default Values + XLS filenames

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é

RE: DTS Default Values + XLS filenames

(OP)
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!"

RE: DTS Default Values + XLS filenames

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.

RE: DTS Default Values + XLS filenames

(OP)
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!"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close