SSIS Datasource query
Does SQL Server 2005 SSIS Datasource from SQL Server query have any issues with Table Variables?
In my select I have (partial of course)
--Get data for export
DECLARE @Results TABLE
(
EmployeeNumber INT NOT NULL,
DefCode VARCHAR(10) NOT NULL,
HOURS NUMERIC(5,2) NULL,
OTHours NUMERIC(5,2) NULL,
CodedHours NUMERIC(5,2) NULL
)
INSERT INTO @Results
( EmployeeNumber ,
DefCode,
HOURS ,
OTHours ,
CodedHours
)
SELECT A.[Employee Number] EmployeeNumber,
etc...
The SSIS sees the select in preview fine...
But...again...does it have any issues with consuming that?
I'm getting an empty file when I run the package.
In my select I have (partial of course)
--Get data for export
DECLARE @Results TABLE
(
EmployeeNumber INT NOT NULL,
DefCode VARCHAR(10) NOT NULL,
HOURS NUMERIC(5,2) NULL,
OTHours NUMERIC(5,2) NULL,
CodedHours NUMERIC(5,2) NULL
)
INSERT INTO @Results
( EmployeeNumber ,
DefCode,
HOURS ,
OTHours ,
CodedHours
)
SELECT A.[Employee Number] EmployeeNumber,
etc...
The SSIS sees the select in preview fine...
But...again...does it have any issues with consuming that?
I'm getting an empty file when I run the package.
lcohan
Do you have the code above with @table in SSIS T-SQL or SQL Stored proc executed in SSIS Step? If it's not a SQL Stored Proc why not create one because you can test it via SSMS query to make sure the output is OK then just exec it in SSIS T-SQL task.
ASKER
Icohan,
I did both. Select and Stored procedure as well.
They both show rows...but don't actually output
I did both. Select and Stored procedure as well.
They both show rows...but don't actually output
"They both show rows...but don't actually output" - sorry but I don't quite understand...
Lets take it one step at a time - did you executed the SP in SQL query and it returns expected result set? Or does the action as it's expected in the database?
Lets take it one step at a time - did you executed the SP in SQL query and it returns expected result set? Or does the action as it's expected in the database?
ASKER
Icohan
When I execute the stored procedure in SQL Server 10 rows are returned
When I execute the stored procedure in SSIS in the "Preview" Source
It returns 10 rows...exactly what I want
If I say to add headet text...
It's added to the text file I'm outputting
The only thing it's NOT doing is exporting the data to my text file.
When I execute the stored procedure in SQL Server 10 rows are returned
When I execute the stored procedure in SSIS in the "Preview" Source
It returns 10 rows...exactly what I want
If I say to add headet text...
It's added to the text file I'm outputting
The only thing it's NOT doing is exporting the data to my text file.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Found my own solution