Variables in OLEDB datasource.
I would like to use the following SQL comand as my Datasource. INSERT INTO @Adults SELECT 5 INSERT INTO @Adults SELECT 6 INSERT INTO @Adults SELECT 7 --SELECT * FROM @Adults DECLARE @Children TABLE (Children INT) INSERT INTO @Children SELECT 1 INSERT INTO @Children SELECT 2 INSERT INTO @Children SELECT 3 --SELECT * FROM @Children DECLARE @Beds TABLE (Beds INT) INSERT INTO @Beds SELECT 1 INSERT INTO @Beds SELECT 2 INSERT INTO @Beds SELECT 3 INSERT INTO @Beds SELECT 4 --SELECT * FROM @Beds Select * from @Adults CROSS JOIN @Children CROSS JOIN @Beds SSIS does not seem to generate any data from the query. Can I not use SQL variables?Mr Shaw
February 26th, 2011 6:19am

I do not see @Adults declared. But I think the table variable is not playing well here. I would do it a stored procedure rather than having it as a T-SQL batch.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 6:35am

I declared @Adult and it didn't return any values. Preview showed what I expected, but running it didn't work. I converted this to a stored procedure and got an error in the PreExecute of the DataFlow: [OLE DB Source [192]] Error: A rowset based on the SQL command was not returned by the OLE DB provider. I set the ValidateExternalData on the data source to false and I also got the same error. I changed the table variables to temporary tables and I can't even preview. I think that there is a blog about this somewhere.Russel Loski, MCT
February 26th, 2011 6:54am

Did you set Rsultset to "Full Resultset"?
Free Windows Admin Tool Kit Click here and download it now
February 26th, 2011 7:05am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics