Post reply

Dynamically exporting text file from SQL table using DTS

  • May 11, 2006 at 7:28 am

    #76524

    Hi,

    Currently I have two SQL tables with different structure

    I have to transfer the records from each SQL table to corresponding text file.

    That is for example I have

    T1 and T2 SQL tables with different structure

    When I am executing the DTS, the DTS has to transfer the records from T1 SQL table to C:\Temp\T1\t1.txt

    and transfer the records from T2 SQL table to C:\Temp\T2\t2.txt

    I am using global varibles for the following items:

    SQL Table,

    Server Name,

    Database Name

    Destination Location

    Destination file

    It is working fine if I define two different DTS.

    But I want one DTS to do the above job

    Can any one please help me

    Thanks

     

  • May 11, 2006 at 3:51 pm

    #637413

    If you want both of these to run at the same time then create yourself 3 datasources. 1 for you SQL Server (Source) and 2 texts files for your destinations.

    Create a datapump from the SQL Source Server to the 1st text file destination (C:\Temp\T1\t1.txt) and populate that with whatever SQL you are using for t1, transformations etc. Then create a seperate datapump from the same SQL Server Source to the 2nd text file destination (C:\Temp\T2\t2.txt) with the T2 SQL in it.

    Not certain how you are using your global variables and so on but when I do this I have the text file destinations populated by a Dynamic Properties Task, based on an ini file in the directory or another table somewhere else. In your example it might simply be a case of creating another set of variables for the 2nd datapump and telling the Dynamic Properties Task to use them for the 2nd text file destination?

    When you run it will carry out both datapumps at the same time....

    Unless this is what you've already tried and there's some problem with the global variables that I'm mis-understanding?

    Edited after post as it keeps turning my brackets into smileys

  • May 12, 2006 at 9:02 am

    #637595

    Hi Mike,

    Thanks for your reply

    Yes I tried the option already as you mentioned. But my requirement is not only two tables. It is n number of tables

    That is currently I am working on a interface project. Some of the table values need to be populated to text files.

    Currently four tables in my lap. It may increase in future. What I need is One source and one destination and one datapump

    In the Datapump I can define a default table settings (Source SQL table and Destination Location and the Destination Text file name and the Transformations)

    What I tried was, just created as above and created one Activex file

    By using the Global varibale/Dynamic Property, i did the follwoing code

     

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

     If DTSGlobalVariables("gUserCode").Value="D"  Then

      DTSDestination("Patient_Number") = DTSSource("Patient_Number")

      DTSDestination("User_Code") = DTSSource("User_Code")

      DTSDestination("FL_Admission_Number") = DTSSource("FL_Admission_Number")

      DTSDestination("FL_Sub_Contract_ID") = DTSSource("FL_Sub_Contract_ID")

      DTSDestination("FL_Provider_Info") = DTSSource("FL_Provider_Info")

      DTSDestination("FL_Nationality") = DTSSource("FL_Nationality")

      DTSDestination("FL_Race_Code") = DTSSource("FL_Race_Code")

      DTSDestination("FL_Gender") = DTSSource("FL_Gender")

      DTSDestination("FL_SSN") = DTSSource("FL_SSN")

      DTSDestination("FL_Employer_Id_Number") = DTSSource("FL_Employer_Id_Number")

      Main = DTSTransformStat_OK

     ElseIf DTSGlobalVariables("gUserCode").Value="P"  Then

      DTSDestination("User_Code") = DTSSource("User_Code")

      DTSDestination("FL_Disability_Income") = DTSSource("FL_Disability_Income")

      DTSDestination("FL_Prognosis") = DTSSource("FL_Prognosis")

      DTSDestination("FL_Legal_Status") = DTSSource("FL_Legal_Status")

      DTSDestination("FL_Admit_Type") = DTSSource("FL_Admit_Type")

      DTSDestination("FL_Days_Spent") = DTSSource("FL_Days_Spent")

      DTSDestination("FL_Days_Worked") = DTSSource("FL_Days_Worked")

      DTSDestination("FL_Employement_Income") = DTSSource("FL_Employement_Income")

      DTSDestination("FL_Subsidy_Income") = DTSSource("FL_Subsidy_Income")

      DTSDestination("FL_Other_Income") = DTSSource("FL_Other_Income")

      DTSDestination("FL_GAF_Score") = DTSSource("FL_GAF_Score")

      DTSDestination("FL_Total_School_Avail_Days") = DTSSource("FL_Total_School_Avail_Days")

      DTSDestination("FL_Total_School_Attend_Days") = DTSSource("FL_Total_School_Attend_Days")

      DTSDestination("FL_CGAS_Score") = DTSSource("FL_CGAS_Score")

      DTSDestination("Assessment_Sequence_Number") = DTSSource("Assessment_Sequence_Number")

      DTSDestination("Element_Code") = DTSSource("Element_Code")

      DTSDestination("Key_Column") = DTSSource("Key_Column")

      Main = DTSTransformStat_OK

     End If

    End Function

    But after running the DTS I am getting the following Error in both the text files.

    As follows:

    DTSRun:  Loading...

    DTSRun:  Executing...

    DTSRun OnStart:  DTSStep_DTSDynamicPropertiesTask_2

    DTSRun OnFinish:  DTSStep_DTSDynamicPropertiesTask_2

    DTSRun OnStart:  DTSStep_DTSDynamicPropertiesTask_1

    DTSRun OnFinish:  DTSStep_DTSDynamicPropertiesTask_1

    DTSRun OnStart:  DTSStep_DTSActiveScriptTask_1

    DTSRun OnError:  DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)

       Error string:  Error Code: 0

    Error Source= Microsoft VBScript runtime error

    Error Description: Type mismatch: 'DTSSource'

     

    Error on Line 6

    Am i missing anything?. Can you please help

     

    Thanks

  • May 12, 2006 at 9:17 am

    #637601

    The n tables... are they the same structure, just with differing data?

    If so you could have 1 data pump with an interative loop controlled by your ActiveX.

     

    Mike

  • May 12, 2006 at 10:18 am

    #637624

    Hi Mike,

    No the SQL tables are of different structure

     

     

  • May 12, 2006 at 1:18 pm

    #637680

    I would be inclined to try something from the command line for something like this.  It would look something like:

     

    ISQL -SMyServer -E -Q"SELECT * FROM db.owner.MYTABLE" >> c:\temp\myfile.txt

    Other options are available for the ISQL.  Enter ISQL /? for details

    Good Luck!

  • May 14, 2006 at 1:06 am

    #637734

    Hi Dennis,

    Thanks for the reply.

    I tried the command

    ISQL -SAK -E -Q"SELECT * FROM BUT.dbo.Pat" >> c:\temp\myfile.txt

    (AK is my server name and BUT is my DB and Pat is my table)

    and I am getting the following errror:

     

    But I tried the following command and it is saving the results in the output file

    oSQL -SAK -E -Q"SELECT * FROM BUT.dbo.Pat" >> c:\temp\myfile.txt

    But it is giving the Column names and do not want that to be in my Export file

    Of Course if we execute the select query in the SQL Query analyzer and set the option as "Print Column Headers" Check box to off it is giving the results with no column headings.

    But what option I need to add to off the column headings printing in the OSQL query

    Thanks

     

  • May 14, 2006 at 1:18 am

    #637735

    Hi Dennis,

    I checked the MSDN and there is an option for the above as -h-1 to not to print the column headings.

     

    But query result saved in the file is breaking into two many lines (line separator). But I need the results should be saved in a single line for each row

    In the oSQL help command it says its maximum size is 80 char. Is there any way to achieve the above

    Thanks in advance

     

     

     

  • May 14, 2006 at 2:17 am

    #637736

    Thanks Dennis,

    I checked the same in MSDN and it is mentioned the width option also as -w<n>

    and it worked well.

    Here is the statements I executed

    set @query= 'select * from tablename '

    set @FilePath='c:\temp\t.txt'

    set @cmd = 'osql -S'+@Server_Name+' -E -h-1 -w1000 -Q'+ '"'+@query+'"'+ ' >> ' + @FilePath

    --print @cmd

       exec master.dbo.xp_cmdshell @cmd,no_output

    Dennis Thanks a lot for the help

    But still I am very curious about working with DTS for the same

    It will be helpful if any one help me to understand how the problem can be solved using DTS.

    Thanks a lot

     

Viewing 9 posts - 1 through 8 (of 8 total)

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