metadata issue

db042190 1,516 Reputation points
2020-08-18T20:54:41.257+00:00

hi , i'll post the rest of the question once i see this new forum doesnt bounce me out

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
0 comments
Accepted answer
  1. db042190 1,516 Reputation points
    2020-08-19T19:02:16.327+00:00

    separating the create out into its own component and delaying validation on the component i first described did the trick. i never took the time to separate the original into two batches (go between). That occurred to me too.

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. db042190 1,516 Reputation points
    2020-08-18T21:00:35.417+00:00

    i'm getting an error on an oledb source whose t-sql has the following form... 

    create table ##xcandidates(x int,y int, z uniqueidentifier)
insert ##xcandidates
output inserted.* select distinct...

    the error code is 0x80004005 and says ...metadata discovery only supports temp tables when analysing a single-statement batch. what is the workaround? sorry i didnt see a way to update the original post.

    0 comments
  2. Monalv-MSFT 5,896 Reputation points
    2020-08-19T02:44:11.993+00:00

    Hi db042190,

    According to my test, we should run the t-sql about creating ##Temp table in SSMS first.

    Then we can read the data of ##Temp table in OLEDB Source using the following two methods:

    1. Set Data access mode as SQL command.
    2. Or set Data access mode as Table name or view name variable.

    Please refer to the following pictures:

    18613-createtemptableinssms.png

    18614-sqlcommand.png

    18636-table-nameor-view-name-variable.png

    18615-variable.png

    Best Regards,
    Mona

    ----------

    If the response is helpful, please click "Accept Answer" and upvote it.

  3. Yitzhak Khabinsky 25,721 Reputation points
    2020-08-19T13:48:53.64+00:00

    Hi db042190,

    You can try the following:

    • Create a stored procedure, and put there all the T-SQL statements.
    • Call that stored procedure from SSIS OLEDB Source.
    0 comments
  4. db042190 1,516 Reputation points
    2020-08-19T15:39:44.447+00:00

    cant really do that yitzhak cuz the select is built dynamically. dont really want to pass the query to a proc and get involved in dynamic sql. i think i'm going to try to create the table in a previous sql task, and delay validation on this component.

    does anybody know the diff between responding like this and entering a comment like i did for mona's post? also i got here today by entering my userid but how do you get to the ssis topics in this new forum?