It’s a common practise to use Temp tables inside SP’s to
optimize the performance but when you will call the same SP in SSIS which is
using temp table inside it you will get the below error.
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Source [1]]: No column
information was returned by the SQL command.
Error at Data Flow Task [SSIS.Pipeline]: "output
"OLE DB Source Output" (11)" contains no output columns. An
asynchronous output must contain output columns.
Error at Data Flow Task [SSIS.Pipeline]: One or more
component failed validation.
Error at Data Flow Task: There were errors during task
validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Below is the test script of table
.
CREATE TABLE [dbo].[LKP_Countries](
[Country] [varchar](100) NULL,
[Code] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'INDIA', 91)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'SINGAPORE', 65)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'USA', 1)
GO
INSERT [dbo].[LKP_Countries] ([Country], [Code]) VALUES (N'PAKISTAN', 92)
GO
Now we will create a Stored Procedure that will have a temp
table inside it using the above table
create PROC USP_COUNTRIES
@CODE VARCHAR(200)
AS
BEGIN
DECLARE @COUNTRYCODE VARCHAR(200)
SET @COUNTRYCODE=@CODE
SELECT * INTO #COUNTRIES FROM [DBO].[LKP_COUNTRIES]
IF @CODE=91
SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
ELSE
SELECT * FROM #COUNTRIES
END
Next step we will open SSIS and drop an oledb connection
into it
Will call the above SP in oledb destination and map the code
parameter accordingly
Since SSIS does mapping in design time so if you will click
on mapping you will get the below error with no column information available as below.
The component reported the following warnings:
Error at Data Flow Task [OLE DB
Source [1]]: No column information was returned by the SQL command.
Now click ok and try to run the package you will get the
below error.
Solution:
Since SSIS does a mapping in design time so will generate a
fake condition 1=0 or 1=2 in the SP which contains all the SP’s columns and help SSIS
to generate the column list in the design time as below as highlighted.
ALTER PROC USP_COUNTRIES
@CODE VARCHAR(200)
AS
BEGIN
DECLARE @COUNTRYCODE VARCHAR(200)
SET @COUNTRYCODE=@CODE
IF 1=0
BEGIN
SELECT CONVERT(VARCHAR(200), NULL) AS CODE,
CONVERT(VARCHAR(200), NULL) AS COUNTRY
END
SELECT * INTO #COUNTRIES FROM [DBO].[LKP_COUNTRIES]
IF @CODE=91
SELECT * FROM #COUNTRIES WHERE CODE=@COUNTRYCODE
ELSE
SELECT * FROM #COUNTRIES
END
Since we have only two columns
in our select so we have added both the column name
Note : You have to add
all the column names in the 1=0 condition which you want in your SSIS Package.
Once you have altered
your SP Go to SSIS click on mapping now this time it will map all the columns
Add a Flat file destination Map the column names and Run
your SSIS Package.
It will export all result to your destination.
Note:You can also use SET FMTONLY ON; above your sp to get the temp table column information but it has disadvantage that to get the meta data information it used to execute the SP five times.
