metadata issue

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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:

Best Regards,
Mona

----------

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

Answer 4

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.

Answer 5

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?