Post reply

Exporting From SQL Server to Text file using DTS

  • November 15, 2006 at 3:01 am

    #67882

     wanted to know if I could Export a Table to a Text File. Now the complex thing is the File Name is to be Dynamically Generated. It is based on today's date. So if the date today is 11/15/2006 then the file name has to be Extract-20061115.txt.

    I am trying this in a DTS package but till now no luck.

    Through the Avtivex I have generated the file name

    Function Main()

    DTSGlobalVariables("FileName").value = "U:\Test\Extract-" + cstr(year(date)) + cstr(month(date)) + cstr(day(date)) + ".txt"

    rem MsgBox DTSGlobalVariables("FileName").value

    Main = DTSTaskExecResult_Success

    End Function

    But do not know how to use this global variable value as the destination table name.

    Thanks

    George

  • November 15, 2006 at 5:15 am

    #671906

    Hi

    I am doing same thing mine is working & i am exporting this file every morning to client computer try this:-

    globalVal = DTSGlobalVariables("global").Value

     set dtsCurrentDate = DTSGlobalVariables("Currentdate")

    DTSGlobalVariables("FileName").value = "U:\Test\Extract-" & Year(dtsCurrentDate) & Right("0" & cstr(Month(dtsCurrentDate)),2)   &  Right("0" & cstr(Day(dtsCurrentDate)),2)   &   Right("0" & cstr(Hour(dtsCurrentDate)),2)   &  Right("0" & cstr(Minute(dtsCurrentDate)),2) &".csv" 

  • November 15, 2006 at 6:00 am

    #671916

    Hey Vandy, Thanks for the help.

    Can u tell me what is it that i have to do after i get the file name.

    I really am confused on how am i supposed to use this file name further.

    Thanks

    George

     

     

     

  • November 15, 2006 at 8:55 am

    #672008

    Instead of using a Global Variable, you can change it at run time via an activeX script. 

    Have a look at this and see if it helps any.

    http://www.sqldts.com/default.aspx?200

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • November 15, 2006 at 11:51 pm

    #672197

    Hi,

    The best thing to use here after you have your filename in a global variable is a dynamic properties task. You will see it on the left hand side in the task section. With this task, you can set the destination of your text file export to the global variable at runtime. I'm sure this will work in your situation.

    Martin.

  • November 16, 2006 at 6:50 am

    #672248

    Hi Geogre

    Could you tell where you want to export your text file like i am exporting my file to other server with FTP ....

  • November 16, 2006 at 10:59 pm

    #672515

    Thanks Vandy, one of my colleague found how to do it, the following is the code.

     

     

    Function Main()

    dim dtNow, TimeNow

    dtNow = Date

    dtNow = replace(dtNow,"/", "-")

    TimeNow = Time

    TimeNow = replace(TimeNow ,":", "-")

    Dim Mainfile, ExportFile

    Set Mainfile = CreateObject("Scripting.FileSystemObject")

    Set ExportFile = Mainfile.GetFile("\\daldevsql2\c$\ppk\ebiz.xls")

    ExportFile.Copy ("

    \\daldevsql2\c$\ppk\ebiz_summary_" & dtNow & "_" & TimeNow & ".xls")

    Main = DTSTaskExecResult_Success

    End Function

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply