| Author |
 Topic  |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
 Posted - 29 October 2003 : 18:38:48
|
VB6 is not required
You need to create a DTS package in Sql Server and schedule it to run when you want.
Go to your Sql Server db
Go to Data Transformation Services
Go to Local Packages, right click and click New Package
Drag and drop Text File Source from the left pane to the right and set the source.
Drag and drop Microsoft OLEDB driver for Sql Server as above
Configure the transformations between the text file and db table
save DTS package
schedule it!
|
The UK MkIVs Forum |
 |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
Jeepaholic
Average Member
USA
697 Posts |
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 29 October 2003 : 21:52:34
|
| Yea DTS was simple, but now I have created the DTS package but it saves it in SQL Server and I want the file saved so I can run the file from a different computer and the location of the text file I upload is not always going to be the same name or location. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 30 October 2003 : 04:34:53
|
You can save a DTS package to a file, it's a bit tricky to find but it's there somewhere.
Can't think of a solution to the filename problems, i've always done this using the same names and locations. |
The UK MkIVs Forum |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 30 October 2003 : 09:59:42
|
I had the same deal. I needed to runa package ona machine that didnt run SQL Server, itjust had the Enterprise Manager on it. Here is what I did:
I saved my DTS package locally as a "Structured Storage File"
I then set up Windows task scheduler to run "dtsrun" every night a midnight:
"dtsrun.exe /F c:\path\YourDtsPackage.dts /N PackageName"
|
 |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 30 October 2003 : 10:47:44
|
Here's how to save a DTS Package:
Open the package
Go to Package > Save As
Change location to Structured Storage File
I will save as PackageName.dts |
The UK MkIVs Forum |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 30 October 2003 : 12:01:35
|
quote:
Originally posted by redbrad0
Yea DTS was simple, but now I have created the DTS package but it saves it in SQL Server and I want the file saved so I can run the file from a different computer and the location of the text file I upload is not always going to be the same name or location.
The same name is not so much an issue. If you place it in it' sown directpry, you can create an activex script and use FSO to rename the file whatever you want. Or yo can find the name of the file and set up a global variable that can be accessed by any process in the package.
The different location is a tougher one. What type of location will it be? ftp site, http site, different drives, etc?? |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 30 October 2003 : 12:18:40
|
| Well I should be able to make it always be stuck in one directory, but wish there was a way you could create a DTS package and have it read the variables from a text file on what file to open, where to send it (sql server address and password), just all of those kinds of things. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
redbrad0
Advanced Member
USA
3725 Posts |
Posted - 30 October 2003 : 12:36:24
|
| I just had DTS create a .bas file so I can create a VB6 program to run the file. I think this will be easier for everything I need to do because I can then specify the file to import, location and everything else. |
Brad
Oklahoma City Online Entertainment Guide
Oklahoma Event Tickets |
|
|
|
snaayk
Senior Member
USA
1061 Posts |
Posted - 30 October 2003 : 15:59:27
|
FYI, you can create a global variable to change the file name & location.
For example:
textfile = "text.txt"
DTSGlobalVariables("filename").value = textfile
You can use FSO to read the variables off of a text file. |
|
|
| |
Topic |
|
Topic Locked
