JavaBoy060299
asked on
How to execute DTS from stored procedure
Hi,
I need to import a text file (fixed-length) into the table.
I have created a DTS package to automate the process, but now i want to do it programmatically from the web application.
I'm thinking to execute the DTS package from a stored procedure, the stored procedure must have texfile pathname, destination server and or table.
subsequently the stored procedure will be called from my web application.
anyone can help me to accomplish the task?
Thanks in advance,
JavaBoy
I need to import a text file (fixed-length) into the table.
I have created a DTS package to automate the process, but now i want to do it programmatically from the web application.
I'm thinking to execute the DTS package from a stored procedure, the stored procedure must have texfile pathname, destination server and or table.
subsequently the stored procedure will be called from my web application.
anyone can help me to accomplish the task?
Thanks in advance,
JavaBoy
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Dishan,
could you give me an example on how to pass the value into @GlobalVariableList and use it to call the DTS package ?
for my case i need to pass the text file name (full path) that will be imported into the table.
Thank you.
could you give me an example on how to pass the value into @GlobalVariableList and use it to call the DTS package ?
for my case i need to pass the text file name (full path) that will be imported into the table.
Thank you.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_misc_68tv.asp
http://www.sqldts.com/default.aspx?104
Use /A
xp_cmdshell 'dtsrun /~Npackagename /~Avariablehere'
See how to specify typeid in this link:
http://www.docendo.se/mspress/msp_online/samplechapter/0735612714.htm
HTH
http://www.sqldts.com/default.aspx?104
Use /A
xp_cmdshell 'dtsrun /~Npackagename /~Avariablehere'
See how to specify typeid in this link:
http://www.docendo.se/mspress/msp_online/samplechapter/0735612714.htm
HTH
Like was said above, you use the /~A command line variable to pass parms.
ASKER
Hi all,
thanks. I think we are almost there :)
but i was wondering when i call the dtsrun as following:
xp_cmdshell 'dtsrun /S CITLOAN100\NETSDK /U sa /P password /N SAT_Upload /A filename:8=d:\admsustf\sat
it always imports default file in the package (2dec.txt) instead of global variable specified in dtsrun ???
how to make use of global variable ?
note: in my DTS package i have specified d:\admsustf\sat\2dec.txt.
Brett: UNLESS, you go into sql server agent properties and allow the proxy account to execute without being a member of the sysadmins group.
do you mean i have to schedule the package first? how to allow the proxy account to execute ... ?
thanks. I think we are almost there :)
but i was wondering when i call the dtsrun as following:
xp_cmdshell 'dtsrun /S CITLOAN100\NETSDK /U sa /P password /N SAT_Upload /A filename:8=d:\admsustf\sat
it always imports default file in the package (2dec.txt) instead of global variable specified in dtsrun ???
how to make use of global variable ?
note: in my DTS package i have specified d:\admsustf\sat\2dec.txt.
Brett: UNLESS, you go into sql server agent properties and allow the proxy account to execute without being a member of the sysadmins group.
do you mean i have to schedule the package first? how to allow the proxy account to execute ... ?
No, In Enterprise manager if you right click on your sql agent and choose properties, you will see the "Job System" tab. Under this tab there is the "Non Sysadmin job-step" item. This is the proxy account.
Again, I don't encourage anyone to enable this because of the security hole....
Again, I don't encourage anyone to enable this because of the security hole....
ASKER
Brett,
thanks for your note.
all:
anyone can tell me how to make the file name of text file (source) to be dynamic ?
i need to pass the file name as a parameter.
Brett, any comment?
thanks for your note.
all:
anyone can tell me how to make the file name of text file (source) to be dynamic ?
i need to pass the file name as a parameter.
Brett, any comment?
Hi JavaBoy
@GlobleVariableList varchar(500) --"/A <GlobalVariableName>:<data
this is how should pass values ... to sp "/A <GlobalVariableName>:<data
@GlobleVariableList varchar(500) --"/A <GlobalVariableName>:<data
this is how should pass values ... to sp "/A <GlobalVariableName>:<data
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This will open the package properties window. go through the Connection Node and Click Set btn.
ASKER
Thanks everyone.
I hope i have given a fair points for all of you guys.
namasi_navaretnam: sorry no points for you, since your comments similar to others. thanks for the links anyway.
I hope i have given a fair points for all of you guys.
namasi_navaretnam: sorry no points for you, since your comments similar to others. thanks for the links anyway.
One thing to note, if you are using XP_CMDSHELL 'DTSRUN ' you can not run the server in fiber mode, as far as I know.
Not a big problem, but if you get problems at some stage this may be the cause.
Not a big problem, but if you get problems at some stage this may be the cause.
ASKER
I have followed your instruction, how DTSRUN is being called would be like this:
DTSRun /~Z0x4D452BBE34D2D8174E8EE
sorry, I don't understand about global variables, could you elaborate more?
how to define the global variables etc.