| Author |
Topic |
|
raritan
Starting Member
39 Posts |
 Posted - 2007-05-23 : 11:36:58
|
| I am very new to using DTS packages, in fact all I've done so far is created a DTS package using the import file wizard in SQL enterprise manager.I have a package set up that will import a fixed field text file into a SQL database table and append the data to the existing table, and it executes just fine.The only problem is, I need to import a different text file every day, so the name is going to be different each time. Is there a way I can modify this existing DTS package so that it prompts me for a source file name each time I execute it, and if so, how?? Would appreciate any help at allThank youKevin |
|
|
sshelper
Posting Yak Master
216 Posts |
Posted - 2007-05-23 : 11:48:44
|
| Here's a link that may be able to help you:http://www.sqldts.com/246.aspxSQL Server Helperhttp://www.sql-server-helper.com |
 |
|
|
raritan
Starting Member
39 Posts |
Posted - 2007-05-23 : 15:35:29
|
| I see on that page they are showing the underlying vbscript behind the package, but how do I take the package that I've already created and see that vbscript? Is that possible, to take what I already have, and modify it on the backside, so that it prompts for a source file instead of just using the source file that I originally used when I created the dts package?How do I open it so that I can try to make that change? Because right now all I can do is right-click on it and select 'design package', and see it mapped out, but I cannot figure out how to see the vbscript as well. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-24 : 04:35:02
|
The VBScript that is presented is the contents of the ActiveX script tasks in the flow, not the code underlying the package itself. Each is numbered and named so you can match the code to the task. I'm assuming that you currently have no script tasks in your package, so these will need to be added as per the example. quote:
Is there a way I can modify this existing DTS package so that it prompts me for a source file name each time I execute it, and if so, how??
I would advise against making the package interactive, as this could be disasterous if you ever wanted to schedule it.Mark |
|
|
|
raritan
Starting Member
39 Posts |
Posted - 2007-05-24 : 11:30:00
|
| This package will never be scheduled, how it works is every day a staff person in our office downloads another text file made available on an ftp site, the file is then saved into a directory with all the other previously downloaded files. After doing that, we want the person to manually execute the dts package, and then ideally the dts package will prompt the person to enter the name of the new text file. So the path to the file will always remain the same but the filename will change each day. I don't foresee setting this up on a scheduleI continue looking at the example to determine how to add vbscript tasks to the package, if (probably when) I need additional help I'll post backthankskevin |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-25 : 05:31:39
|
Is this some kind of job creation scheme!?  Why do you want a user to do this manually? I'd schedule the package to run every few minutes and have it pick up any files in a given directory, moving them to a 'processed' directory once finished. But, hey, that's just me.Mark |
|
|
|
raritan
Starting Member
39 Posts |
Posted - 2007-05-25 : 09:50:28
|
| how would I go about doing this then? I'm willing to do whatever works especially if it will make the process even easier, but I'm new at this and need some help with 'how' to set this stuff upthanksKevin |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-25 : 10:09:20
|
Hi Kevin, The link that sshelper provided does exactly this using the FileSystemObject (FSO) within an ActiveX script task. You can download the package from the "Looping, Importing and Archiving" link on this page: [url]http://www.sqldts.com/271.aspx[/url]. It's then just a case of setting the global variables to point to the correct folders, and configuring the datapump to work with your file structure. You can then schedule the package by right-clicking it within EM and selecting "Schedule". The important thing to remember though, is that it will then run on the server under the same account as the SQL Agent service, so this will need to have all the appropriate database and file system permissions. I'd advise setting up some test folders on a dev machine and having a play around with the example package. Give me a shout if you run into any problems - it's been a year or two since I last did any DTS, but I'll do my best to help  Mark |
|
|
|
raritan
Starting Member
39 Posts |
Posted - 2007-05-25 : 10:14:55
|
| Thank you Mark, I'll download and start messing w/ it - if I have problems I'll let you knowKevin |
|
|
|
raritan
Starting Member
39 Posts |
Posted - 2007-05-25 : 12:33:09
|
| Mark I can see in the vbscript under step 2 where you define the global variables, that it refers to, for example, gv_filelocation on one of the lines of script. But I cannot find anywhere where it assigns a value to gv_filelocation? I apologize if these seem like easy questions, but where do I specifically go in this package to modify things, to tell it that my sources files are all in a particular folder, I want them archive folder to be this particular folder, etc. If there's any information you need from me please let me know. Any help would be greatly appreciatedThanksKevin |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-05-29 : 04:04:15
|
| Hi Kevin, You need to set these up in the "Global Variables" tab of the "DTS Package Properties" dialog. You can access this by right-clicking on an empty patch of design pane, and selecting "Package Properties".Mark |
|
|
|
|
DTS import - different source file each time??