Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Red face Unanswered: Exporting to multiple files from a single source table using DTS

    Hi Friends

    I have been trying to solve this problem for the last 2 days but no luck.

    Here is the problem that I am facing.

    The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

    I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

    Can anyone please help me.

    Thanks in advance.

    Srinivas.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Exporting to multiple files from a single source table using DTS

    Can't you split up your export by using views with restrictions on your recordtype instead of your table?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    manthenasri, does DTS allow you to redirect output on the fly? From what I have seen the source and destination is fixed at design time.

    Have you tried BCP with a query? You could use DTS to script your BCP commands to a CMD file. Then at the cmd prompt you could use DTSRUN to build the CMD file and then execute the CMD file. Or just set up a procedure to script the BCP commands and use xp_cmdshell.

    Sort of bassackwards but it's the best I could come up with.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Aug 2003
    Posts
    3

    This is what my business logic would be like

    Hello Paul

    I do not know much about BCP. But here is the problem that i have to solve.

    I have a table by name T1 which has the following data

    Business Unit Name SSN RATE
    1 Joe 923456789 40
    1 Jenny 568234569 50
    3 Mike 234198634 35
    3 Meri 743579374 45

    Now i need to create a two files by name 1.ben and 3.ben on the fly.
    and them put in the data related to Joe and Jenny into 1.ben and the data for Mike and Meri into 3.ben. If there is more data related to other business units i need to create those files also.

    Can you please tell me how i can accomplish using BCP.

    Thanks,
    Srinivas.

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I think the BCP command you want is...

    bcp "select * from <dbname>.<owner>.<table> where <where clause>" queryout <file name> -c -T -S <server>

    probably could wrap it all up like this:
    Code:
    create table manthenasri([Business Unit] int, [Name] varchar(15), SSN varchar(9), Rate int)
    insert into manthenasri values(1,'Joe','923456789',40) 
    insert into manthenasri values(1,'Jenny','568234569',50) 
    insert into manthenasri values(3,'Mike','234198634',35) 
    insert into manthenasri values(3,'Meri','743579374',45)
    
    declare @bu int, @TSQL varchar(255)
    select @bu = min([Business Unit]) from manthenasri
    while @bu is not null begin
      set @TSQL = 'bcp "select * from ' + db_name() + '..' + 'manthenasri where [Business Unit] = ' + cast(@bu as varchar) + '" queryout ' + cast(@bu as varchar) + '.ben -c -T -S ' + @@servername
      print @TSQL
      -- master.dbo.xp_cmdshell(@TSQL)
      select @bu = min([Business Unit]) from manthenasri where [Business Unit] > @bu
    end
    Open Query Analyzer and run the above code. It should produce a few BCP commands. Make adjustments for your table and rerun the script. Next try cutting and pasting the results to a CMD prompt and see how it works. Once that's done you can un-comment the xp_cmdshell call and try running this on your server.


    THIS IS UNTESTED CODE!


    Look this over and post back with questions.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Aug 2003
    Posts
    5

    Re: Exporting to multiple files from a single source table using DTS

    Hi Srinivas ,

    Use DTS designer add one data pump task for each condition and output file.

    For example : Use select statement as the source of data pump task with where clause and desination to the the disired out put file.

    cheers

    Shaji

    Originally posted by manthenasri
    Hi Friends

    I have been trying to solve this problem for the last 2 days but no luck.

    Here is the problem that I am facing.

    The task on had is to transfer data from a single table (the source) to multiple files (Destination) based on the record type.

    I have tried to changing the Datasource property of the Text File Connection object dynamically by using an ActiveX Script. But the data is still being written only to one file.

    Can anyone please help me.

    Thanks in advance.

    Srinivas.

  7. #7
    Join Date
    Aug 2003
    Posts
    3

    Thumbs up Thanks Paul

    Hai Paul


    Thank you for the BCP solution that you have given. I really appreciate you patience in giving me a sample program also.

    The sample worked just fine except for the brackets enclosing the variable @Tsql. I think these brackets should not be there. I had to remove these brackets.

    But now have a problem of reading from multiple files and posting that data into table. I have to do an isert and also an update on the table. How can i do that?

    Thank you very much once again.

    Srinivas.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •