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 |
|
BigBlackTiger
Starting Member
2 Posts |
 Posted - 2008-07-09 : 14:29:09
|
| I need to automate within a DTS Package the Destination Create Table Button that automatically populates the correct column names in a SQL Make Table Query.Back Story:I am taking Excel data from sheet1 and moving it to SQL 2000. The problem is the columns will change without notice monthly, so I need a way to do the following:1. check to see if the SQL table exists2. update the transformations to reflect the new columns3. run the updated package - (Done!) |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2008-07-20 : 21:13:52
|
| Hi, You can programmatically update the transformations via the DTSPackage API but I wouldn't do that if I were you. If you must do this in DTS, I'd suggest doing the whole thing in an ActiveX script. The problem is that you don't know what the data type should be for each column (unless you have a list of known column names and what they should contain). So your script would:- Open the worksheet- Get a list of the column names- Create your table (or check if it exists)- Iterate through each worksheet row and issue an INSERT statement for each.HTH,Tim |
 |
|
|
BigBlackTiger
Starting Member
2 Posts |
Posted - 2008-07-20 : 23:03:00
|
| Thank You I will try that or something very simularAll's Well that ends well! |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2008-07-20 : 23:11:31
|
| Another little tip that I find handy is to code your script in VB or VBA (Access, Excel or something) so you have all the objects etc available and you are able to debug it. Then you can strip out the declaration types and enums and cut & paste into your ActiveX task. Makes things a lot easier IMO.Cheers,Tim |
|
|
|
|
|
|
"Create" Button