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 |
|
jaybee
Yak Posting Veteran
72 Posts |
 Posted - 2007-11-12 : 07:56:45
|
| Foreword - DON'T send me to www.sqldts.com!!!!!! :)What I'm looking for is a <10page article that describes each of the X most commonly used commands in Active X for data. Like FileSystemObject, DTSGlobalVariables etc.I could (and will eventually) buy a book on this but I'm going to be getting involved a lot with package creation very soon, so I'll need a "Read and Run" education. I don't imagine it will take too long, as I'm already familiar with T-SQL, but I don't have a week to do an official course.Thanks,Jaybee. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-11-12 : 08:48:52
|
| those aren't active x data objects...i think u just mean active x objects?try these:fileSystemObjecthttp://www.w3schools.com/asp/asp_ref_filesystem.asphttp://msdn2.microsoft.com/en-us/library/6kxy1a51.aspxActiveX DataObjectshttp://www.w3schools.com/asp/asp_ado.asphttp://msdn2.microsoft.com/en-us/library/ms675532.aspxAnd for dts object library, sqldts.com, which u r alrwady aware of.If u have specific questions about dts object library, post 'em here.:) |
 |
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-11-12 : 13:21:00
|
quote:
Originally posted by russell
those aren't active x data objects...i think u just mean active x objects?
Ok, I'm a DBA not a Developer, scripting is not my forte', hence the thread! I still need some help here. quote:
try these:fileSystemObjecthttp://www.w3schools.com/asp/asp_ref_filesystem.asphttp://msdn2.microsoft.com/en-us/library/6kxy1a51.aspxActiveX DataObjectshttp://www.w3schools.com/asp/asp_ado.asphttp://msdn2.microsoft.com/en-us/library/ms675532.aspx
What the hell?? Those links are for ASP...I'm trying to learn about DTS here, everyone!! quote:
And for dts object library, sqldts.com, which u r alrwady aware of.
Everyone - DON'T send me to www.sqldts.com!!!!!! Did I mention that somewhere before??!?!?!?quote:
If u have specific questions about dts object library, post 'em here.
Just one. What links (apart from damn sqldts.com!) will take me to articles that describe each of the X most commonly used commands in Active X for DTS packages? |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 13:31:18
|
Wow. Awfully testy about sqldts.com.You can write whatever Active x you choose in a DTS package. Sometimes, I do basic file system operations like moving or renaming files as part of a DTS job. Other times, I use the Active x script to handle column specific mapping issues on a bulk insert. I have used it to open, manipulate, and execute macros within Excel files and Access dbs as part of a DTS job.You can use Active x to dynamically create global variables for use later in the DTS package. It all depends on what you are trying to accomplish with the active x as to what the most "commonly used commands" are.Would you like some samples of Active x script?here are some non sqldts linkshttp://msdn2.microsoft.com/en-us/library/aa933459(SQL.80).aspxhttp://msdn2.microsoft.com/en-us/library/aa933534(sql.80).aspxhttp://www.databasejournal.com/features/mssql/article.php/3295691http://doc.ddart.net/mssql/sql70/4_dmotrx_7.htmhttp://search.techrepublic.com.com/search/DTS+ActiveX.html
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-11-12 : 19:02:04
|
| Those examples look like excellent intros to ActiveX scripting in DTS, thanks.As for what I'm trying to achieve, that'll depend on business requirement; I've recently been thrown quite abruptly into this domain, which I've always seen as the pure preserve of the DB developer, and as such I have no idea what I'm going to get asked for.Conversation went something like this on Friday afternoon;"Jaybee, you're a SQL Server pro, and Dave the Developer just walked off site; can you handle the workload?""Nope, I'm a DBA"."But he said you knew how to write Stored Procedures and DTS was easy (note to self - phone Dave to buy him a drink and then pour it over him) - they're part of SQL Server, aren't they?""Yeah, DEVELOPMENT....I can no more put together a DTS package than he can devise a clustering strategy!""Ok, what do you suggest?""Get yourselves another Developer"."No can do, no budget"."Then you're going to have to wait for me to go up a learning curve, the length of which I don't know".Now, I've got lots of articles on DTS package design, but I've noticed that many of our existing packages employ ActiveX scripting, and we're implementing new DB's almost all the time. Most of the ActiveX documentation out there discusses ASP, IIS etc, but nothing (until your post) on how it's used within DTS. Also, most of our existing packages concatenate columns and other functions you described, but I'm told our transformation needs are going to get more, not less complex.The way I look at it is, 80% of the commands used in TSQL are Selects, Inserts/Updates/Deletes, XP_*, Declare etc. I presume the same is true of ActiveX scripting within DTS, so ideally what I'd like is a "glossary", if you will, of those most common commands, and I can simply look in a book/online for the rest as needed. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 19:24:36
|
That isn't something easy to find. Best way is to figure out what type of things YOU will need to figure out and go from there.In ActiveX that I use, I just use the VBScript option because it basically follows VB code. You can write active x script in Java, C# and other languages...so it depends on preference.I use it to perform options not readily available in SQL 2000 DTS T-SQL (like variable assignment of file path names or assignment of dynamic global variables, or file system object type things). Way I learned was trial by fire I suppose, but I had development experience prior to dealing with Active X, so writing simple file system object operations or variable assigment etc was pretty easy to me.Rather than focusing on Active X, look into a book on Visual Basic and/or C# basics. That way the functionality you will need to learn how to use will be covered in there.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-11-12 : 20:18:31
|
| Gotcha. Just one more thing though; you mention you can write Active X in Java and C#; I find that confusing, I thought Active X was a seperate programming language?Forgive my ignorance, but you have to understand where I'm coming from, I'm a DBA and only know enough about programming to understand my own Sprocs; so you can imagine the prospect of learning VB both daunting and mystifying. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 20:31:41
|
It isn't as difficult as you think. In a lot of ways, the language is similar to sql in that you can almost speak it. I mis-spoke too, C# isn't one of the options.If you go to a new DTS package (or an existing one) and click on the Active X Task Properties object to open it up, you can see a language selection drop down, where you can choose VBScript or Java or signedVBScript or signedJavaI use VBScript, because I don't know Java (yet).And i forgive you. six years ago I was a golf pro at a local country club. Now I handle in excess of 1TB of data on a datawarehouse, 50 DTS packages, any number of applications and 40 databases across 2 servers. Believe me, you can find a way to do it (one task at a time..)Don't let it overwhelm you. You can probably figure out temporary ways to scrub data and such in SQL tables using sprocs and T-SQL until you figure out ways to do it in the DTS jobs.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-11-13 : 04:46:48
|
| I presume that's a "Yes" to my question about whether Active X is a separate language! Me, six years ago I was a (qualified) DBA and did my job without having to know one end of a Select statement from another. I do nowadays, but that's as far as I've needed to go. My current contract (expires May) is my last gig in IT, or any other payrolled work, before I begin making money from money. Something of a shame in a strange way, I was just starting to actually ENJOY databasing!! :) |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 13:39:29
|
I guess it is a yes, but as far as you are concerned active x scripting is not active x programming exactly. You only need to know a few bits and pieces of things for DTS transformations, and from the sounds of it basic variable assignment and file system operations..
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
jaybee
Yak Posting Veteran
72 Posts |
Posted - 2007-11-13 : 19:16:14
|
quote:
Originally posted by dataguru1971
I guess it is a yes, but as far as you are concerned active x scripting is not active x programming exactly. You only need to know a few bits and pieces of things for DTS transformations, and from the sounds of it basic variable assignment and file system operations..
Sorry to keep coming back with questions, but could you elaborate on what those bits and pieces comprise? You correctly surmised variable assignment and file system operations, but what else?If I have only a small amount to learn that would be very good news, and quite a relief! |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-13 : 19:31:43
|
How about this. I assume there are already some DTS packages set up by the departed developer. Find out that is relatively simple that has a text file import. OPen that up, and do a SAVE AS to rename it (that way you won't FUBAR it by accident :o) ).When you have this open you should see a text file source and a server icon connected by a black line. Double click the black line and it opens up the actual transformation task for that part. You can kind of explore through some existing stuff that way. When you mouse over part of the package it usually will flash the type of task it is.If you want, screen shot stuff and shoot it to me by email and I can shed some light if you want.The what else? will come by necessity I suppose, but I think if you had a basic idea of active x column transformations within a transformation as well as some basic file operation syntax ( I will send you some in email)..you will be fine.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
|
|
|
|
|
Want to learn Active X Data Objects fundamentals