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