| Author |
Topic |
|
khalik
Constraint Violating Yak Guru
443 Posts |
 Posted - 2003-06-26 : 07:39:49
|
| well in my app which is in VB i am using some dts package and now when i deploy my app on client machine how do i deploy the dts package on the client server....if i save the dts package as vb file (module .bas) then do i need any special tool to run ... like client tool or can just install my app on a empty pc and use it....and is the way to shedule the package at the client sideplease guys try to help me...i have some link which say some thing which did;nt help mehttp://www.mssqlcity.com/FAQ/TranMove/TranPack.htm======================================Ask to your self before u ask someoneEdited by - khalik on 06/26/2003 07:57:58 |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-26 : 08:49:35
|
| You should save your package as Structured Storage File, notas .bas file. Then you can refer to it from VB code, like that:Set oPackage = CreateObject("DTS.Package2")oPackage.LoadFromStorageFile "E:\MyPackage.dts", ""... and so on ...Take note of this DTS.Package2 object. It must be present inWindow's registry on the client machine. |
 |
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2003-06-27 : 06:51:21
|
| thank Stoadbut i think u got wrong... i have a package in my pc i dont want to call externally i want to save it in sqlserver and shedule it...do we have any statments where i can register a DTS package and shedule it...i have checked goPackage.SaveToSQLServer SrvName, UsrName, Pwdif i have this in my code the problem is it will save all the time and generates error is there a way to check if the package is loaded.... or saved. 2 simple one1. how do i register/transfer a DTS Package.2. How do i shedule DTS package by code...VB======================================Ask to your self before u ask someone |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-27 : 07:50:07
|
| 1) In the SQL Server Enterprise Manager console tree, right-click Data TransformationServices, and then click Open Package.In the Select File dialog box, click the .dts file you want, and then click Open.If multiple Data Transformation Services (DTS) packages or package versions weresaved, the Select Package dialog box appears. Click the package or package versionyou want to open.After opening the package you may 'Save as... ' it choosing 'SQL Server' location.Now you can forget about *.dts file. :)2) Set dtsPackage = CreateObject("DTS.Package")dtsPackage.LoadFromSQLServer servername,serverusername,serveruserpassword, ,,,,packagenamedtsPackage.ExecuteHTH :) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-27 : 08:48:36
|
| khalik,(just in case...) forget about *.bas file. Only having *.dts fileyou can transfer a package to another SQL Server (if these servershave no network connection).Plus, having *.dts file you can execute a package from some sp(without any 'registration' this package on the SQL Server):exec master..xp_cmdshell 'dtsrun ...'See dtsrun utility in BOL. |
|
|
|
khalik
Constraint Violating Yak Guru
443 Posts |
Posted - 2003-06-30 : 07:57:13
|
| well thanks a lot Stoad. i have tried all those...in my case is diff... the user is provied with a setup program.. and the user will be using msde provded by me and not a tech person. and i wll not be.. means the setup program shd transfer the dtspackage on the server.when i save as .bas file i am able to run works fine but run only on the system which have client tools. i cannot install on every pc.and when i save it as .dts file as u said i need to open and save it as sql server pacakge. this process i need to automate..and how do i shedule that is unhandled.. any one can give some insight on this...======================================Ask to your self before u ask someone |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-30 : 08:59:45
|
| Now I see what you mean... Let's think. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-06-30 : 09:39:51
|
| Let's try this from a different angle. What does the DTS Package do?Could it possibly be recreated as a S.P. ?JimUsers <> Logic |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-30 : 09:57:16
|
| "Production Environment"?The "client" should not know what technologu that's employed, it should just happen...Everything should run from the serverThere's something missing here...what are you trying to do?Brett8-) |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-06-30 : 15:40:40
|
| You may install SQL Server and copy to its some catalog my.vbs fileand my.dts file.This my.vbs (DTS.Package object is reachable for him) is going to saveto the server your package.Then, your client application setup process may activate this VB script orsend to the server a command like this:cn.Execute "exec master..xp_cmdshell 'my.vbs'"Not hope this helps. :( |
|
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-07-06 : 08:40:42
|
| I've managed to explore a folly workaround for khalik's problem.1) Save this query result in MS Access table. In fact, you savea full copy of your package by this:select top 1 name, '{'+cast(id as varchar(50))+'}' id,'{'+cast(versionid as varchar(50))+'}' versionid,description, '{'+cast(categoryid as varchar(50))+'}' categoryid,createdate, owner, packagedata, owner_sidfrom msdb.dbo.sysdtspackageswhere name='myPackage'2) Then, this module's code in your Access DB saves your packageback to a SQL Server:Dim cn As New ADODB.Connection, rs As New ADODB.RecordsetDim r As DAO.Recordset, iSet r = CurrentDb.OpenRecordset("dts"): r.MoveFirstcn.CursorLocation = adUseClientcn.Open "provider=sqloledb;data source=(local);initial catalog=msdb;" & _"user id=sa;password=;"rs.Open "sysdtspackages", cn, adOpenKeyset, adLockOptimisticrs.AddNewFor i = 0 To 8 ' nine columns in sysdtspackages tablers(i).Value = r(i).ValueNextrs.Updaters.CloseSet rs = Nothingcn.CloseSet cn = Nothingr.CloseSet r = Nothing- Vit |
|
|
|
|
deploy DTS package