Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2006
    Posts
    5

    Unanswered: dts dynamic query & automap columns question

    Hello All,

    I am sorry if this has been asked - I tried searching but the search kept timing out.

    I have designed a DTS package which extracts a query into an excel file.

    It uses a query that changes dynamically based on user preferences, so I have used the dynamic property SourceSQLStatement to feed the exact query into the DTS package.

    The issue, however, is that the query can be run a multitude of ways, and return a different number of columns each time. On one run, the query could return a 3 column record-set, on the next it could return an 8 column record-set.

    Currently, the DTS package errors on each attempt because it expects a certain column set.

    Is there a way to tell it to auto-map the columns at the time it executes? I could not find a dynamic property which did that.

    I would hate to have to set up a different DTS package for each possible column set.

    I am sure I am missing something.

    Thanks in advance.

    - Charles

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would use a sproc with dynamic sql, bcp and xp_cmdshell personally
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2006
    Posts
    5

    dts dynamic query & automap columns question Reply to Thread

    Hi Brett,

    Thanks for the response. I have to admit I'm fairly new to the more complex aspects of SQLServer (Stored Procedures, cmd_shell, etc). What I have so far was built using the Package Designer - to give you an idea of my level of expertise with DTS. But I'm not opposed to learning if that's what it takes.

    Are you aware of any other "simpler" options?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DTS is such a flukey thing...see it's a dangerous drug...you get used to the GUI, the you start to push it...which means you need to start using ActiveX and such.

    What happens then is that DTS acts like a cursor and has to affect rows, one by one. Which slows things down dramatically.

    If you could give me/us and example of what you are trying to do, I'm sure we can get you an elegant/effecient solution.

    Read the sticky at the top of this thread (ok second sticky) and post what it asks for.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2006
    Posts
    5

    dts dynamic query & automap columns question

    lol Brett - you have described my addiction exactly. It all started so innocently...

    I will try as best as possible to meet your requirements.

    The Question: How can I get the result set from an ad-hoc query to export to any format supported by DTS?

    This question does not deal with any specific tables, data or queries. I will use examples.

    User Interface:
    A web-based interface allows a user to create a report from a number of factors, including choosing which fields should display. This allows for variations in the number of fields being generated. The user may also choose from a number of formats.

    The web application then dynamically assembles the query based on the user's selections.

    Example Query A (5 columns / fields in record-set):
    Code:
    select name, address, state, zip_code, phone_number
    from clients
    where zip_code like '06%'
    Example Query B (8 columns / fields in record-set):
    Code:
    select name, address, state, zip_code, phone_number, billing_number, last_invoice_date, billing_cycle
    from clients
    where zip_code like '06%'
    In the DTS Package:
    1) Connection 1 is set to work from a query. The query is set using the Dynamic Properties Task. It could use Query A one time, and Query B another.

    2) Connection 2 is set as the output file type. For our purposes it will be xls.

    3) The transformation between Connection 1 and Connection 2 exports the query results into the xls file.

    The Error:
    When Query A is exported, the DTS has 5 columns to map. When Query B is exported, it has 8 columns to map. The one DTS package errors out if the number of columns are different than it expects to find. It is not possible to limit users to a specific number of columns.

  6. #6
    Join Date
    Jul 2006
    Posts
    87
    How about as a cheap solution for the 5 column solution, just return empty strings for the last three columns, and that way all of the calls will return 8 columns, but in Excel, it will show as 5.

    Will that work for you?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, this is what I would do...

    #1. Make sure all access to the database is done through strored procedures
    #2. Dynamically build a view based on your users requirements. make it so it's a single column concatenated as a tab or comma delimited and you can even add a headr
    #3. bcp out the view with xp_cmdshell

    Let me work up a sample
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2006
    Posts
    5

    dts dynamic query & automap columns question

    Wow Brett! Thanks, I'm intruiged. I can't wait to see it.

    Thanks again.

    - Charles

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What form do you collect the requirements in? Do formulate the query in the front end? Sounds like you do...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2006
    Posts
    5

    dts dynamic query & automap columns question

    Hi Brett,

    You are correct the query if built first in ColdFusion. The user interface is a form through which the fields and associations are made.

    The query is created, then saved in a sql table, then loaded as a dynamic property at runtime.

    Thanks again,

    - Charles

Posting Permissions

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