April 12, 2008 at 8:23 am
I must be missing something...
I rewrote a SP to take advantage of two CTEs. The first pulls into a temp table and the second is a direct SELECT based off the temp table. But for some reason, my OLE DB Source doesn't want to recognize the column list now. In fact, when I look at the data flow task, there's a validation warning, so I edit the source and the mapping list is completely blank.
Has anyone ever seen this behavior before? Is there a solution?
April 14, 2008 at 8:10 am
It's probably the temp table, not the CTE that is the problem.
When the parsing engine normally gets the meta-data for a stored procedure, it parses the statements rather than actually running them and gets the met-data back from your rowset-returning statements. If you have statements returning columns from temp tables, they cannot be returned at design time. This is pretty annoying and I am surprised MS has not fixed it yet - it happens in SSIS and Reporting Services.
If you post the procedure, I can confirm if this is the issue.
A workaround is to use global temp tables (##) while developing and actually create the temp tables in a session in management studio so they are there when SSIS parses the statement.
Another option (that I use a lot) is to create a stub of your procedure that simply returns an empty recordset with all of the correct data types:
CREATE PROCEDURE MyProc
@MyParam INT
AS
SELECT CONVERT(INT,NULL) AS Col1, CONVERT(VARCHAR(5),NULL AS Col2
This will allow you to configure your package. Then, when you are done, you can set the ValidateExternalMetaData property to False, replace the guts of your procedure, and run your package error-free.
April 14, 2008 at 8:18 am
So, once I get everything properly set up, saving the ValidateExternalMetaData to False will allow the package to run?
I hadn't tried using a Proc stub, so of course I wasn't sure if the above would do me any good. I did try manually adding the external columns & output columns, but got all sorts of errors complaining about a lack of a true source input.
April 14, 2008 at 8:19 am
BTW, Pretty sure the issue is the temp table myself since I tried redoing the proc with only the Temp table and not the CTE. Got the same "blank column list" result as with the CTE.
April 14, 2008 at 8:29 am
Using temp tables in your procedures can be a good idea in a lot of cases, but they cause some havoc with some of the included tools.
I create stubs at the top of a lot of my procedures so I can quickly uncomment the stub and comment the code when I need these types of things to work for me.
April 14, 2008 at 1:10 pm
I tried following your suggestion and got an immediate failure.
Error message is: Column "MyColumn" cannot be found at the datasource.
The OLE DB Source has ValidateExternalMetaData set to false. It just doesn't want to work for me. Thoughts?
I can't post the code right now because I need to sanitize it and I just don't have time ATM to do that. But just in case, I'm re-testing the code via SSMS to verify once again that it works.
April 14, 2008 at 1:40 pm
Ok - let me goa bit more step-by-step.
Say you have a procedure:
CREATE PROC MyProc
@Param1
AS
SET NOCOUNT ON
SELECT GETDATE() AS MyVal INTO #tmp
SELECT * FROM #tmp
--END MyProc
SSIS will hate the temp table on this, so we make a stub
CREATE PROC MyProc
@Param1
AS
SET NOCOUNT ON
--Stub Code
SELECT CONVERT(DATETIME,NULL) AS MyVal
--End Stub
/*
SELECT GETDATE() AS MyVal INTO #tmp
SELECT * FROM #tmp
*/
--End MyProc
Now you have a stub procedure that does not return anything but also does not use a temp table.
1) Drag your OLEDB Source onto your data flow.
2) Leave ValidateExternalMetaData set to True.
3) Set your SQL command to the stored procedure and allow the component to configure it's columns
4) Set the ValidateExternalMetaData property to False
5) Save the component (and project)
Now, go back to Management studio - comment out the stub and uncomment the procedure code.
SSIS should not complain because it will not validate the meta-data.
The key is your stub must have exactly the same Meta-Data that the procedure will actually product in it's output or when the package executes it will have a mismatch (only at execution time) and fail.
Also note that I set NOCOUNT ON in the procedure - sometimes the count messages cause SQL to return recordsets with no columns or rows - it's best to avoid this happening.
I hope that helps.
April 15, 2008 at 4:17 am
That's pretty much what I did the first time. Did it again and got the same error message. I'm wondering if it's a combination of the CTEs and the temp table that's causing my problem.
Code is like:
Create Procedure uspGetCustomerInfo
AS
WITH ContractBasics AS
(
Select Distinct Convert(bigint,c.PolicyID) as PolicyID, c.FirstName,
c.LastName, c.LoanDate, c.PolicyNumber, Convert(varchar(8),p.StoreID) as StoreID,
Convert(char(2),(Case when sp.SellerID = '667A' then 'FC'
when sp.SellerID = '340' then 'IL'
when sp.SellerID = '120' then 'MI' else 'Other' end)) as SellerID,
CASE sp.ProductCode WHEN 'XYZ'
THEN (CASE c.FType WHEN 'L' THEN 'ABC'
WHEN 'B' THEN 'ABC'
ELSE sp.ProductCode END)
ELSE sp.ProductCode END AS ProductCode,
Row_Number() Over (Order by PolicyID) as PolicyOrder
from dbo.Contracts c
join dbo.Products p
on c.RetailProductID = p.RetailProductID
join dbo.SellerProducts sp
on p.ProductID = sp.ProductID
)
Select * into dbo.#Temp1 from ContractBasics;
With ContractFinds AS
(
Select PolicyID, FirstName, LastName, LoanDate, PolicyNumber, StoreID,
SellerID, ProductCode, Row_Number() Over (Order by PolicyID) as PolicyOrder
from dbo.#Temp1 t1
where PolicyID not in (Select Distinct PolicyID from dbo.Staging_DimCustomer
where isnumeric(PolicyID) = 1
and LastName = t1.LastName
and EffDate = t1.LoanDate
and StoreID = t1.StoreID
and ProductCode = t1.ProductCode
and SellerID = t1.SellerID )
)
Select PolicyID, FirstName, LastName, LoanDate, PolicyNumber,
StoreID, SellerID, ProductCode
from ContractFinds;
April 15, 2008 at 5:52 am
So far the only resolution I have found is to stick an Execute SQL Task right before my Data Flow, modify the Proc to stick the data in a real table, and then modify the Data Flow to read off the real table.
Grrr. I wish there were an easier way to do this. I really didn't want to have to create another table in my DW.
April 15, 2008 at 5:56 am
So your stub would just be:
Create Procedure uspGetCustomerInfo
AS
[font="Courier New"]SELECT CONVERT(INT,NULL) AS PolicyID, CONVERT(VARCHAR(30),NULL) AS FirstName, CONVERT(VARCHAR(30),NULL) AS LastName
, CONVERT(DATETIME,NULL) AS LoadDate, CONVERT(INT,NULL) AS PolicyNumber, CONVERT(INT,NULL) AS StoreID
, CONVERT(INT,NULL) AS SellerID, CONVERT(VARCHAR(6),NULL) AS ProductCode[/font]
I am not sure how you are getting any errors with this.
Also, your temp table is probably not necessary.
You could do something like this:
[font="Courier New"]WITH ContractBasics AS
(
Select Distinct Convert(bigint,c.PolicyID) as PolicyID, c.FirstName,
c.LastName, c.LoanDate, c.PolicyNumber, Convert(varchar(8),p.StoreID) as StoreID,
Convert(char(2),(Case when sp.SellerID = '667A' then 'FC'
when sp.SellerID = '340' then 'IL'
when sp.SellerID = '120' then 'MI' else 'Other' end)) as SellerID,
CASE sp.ProductCode WHEN 'XYZ'
THEN (CASE c.FType WHEN 'L' THEN 'ABC'
WHEN 'B' THEN 'ABC'
ELSE sp.ProductCode END)
ELSE sp.ProductCode END AS ProductCode,
Row_Number() Over (Order by PolicyID) as PolicyOrder
from dbo.Contracts c
join dbo.Products p
on c.RetailProductID = p.RetailProductID
join dbo.SellerProducts sp
on p.ProductID = sp.ProductID
)
, ContractFinds AS
(
Select PolicyID, FirstName, LastName, LoanDate, PolicyNumber, StoreID,
SellerID, ProductCode, Row_Number() Over (Order by PolicyID) as PolicyOrder
from ContractBasics t1
where PolicyID not in (Select Distinct PolicyID from dbo.Staging_DimCustomer
where isnumeric(PolicyID) = 1
and LastName = t1.LastName
and EffDate = t1.LoanDate
and StoreID = t1.StoreID
and ProductCode = t1.ProductCode
and SellerID = t1.SellerID )
)
Select PolicyID, FirstName, LastName, LoanDate, PolicyNumber,
StoreID, SellerID, ProductCode
from ContractFinds;[/font]
If you are not indexing the temp table, you could just let the optimizer decide if it should be in memory or on disk. You could also probably adjust this with a TOP 100 PERCENT and an ORDER BY to get it to match on the join faster.
April 18, 2008 at 4:36 am
Michael,
Thanks for the redone CTE thing. I'm still learning a lot about these.. But are you able to do a double CTE and still get the fields? Because no matter what I do, my source just can't see them. Even when I drop the temp table.
April 18, 2008 at 5:28 am
Hi, you've probably already done this, but I'm just saying it because looking at the sproc Michael has written he has the SET NOCOUNT ON statement at the top where as in Brandie's version it's not there. Are you definately setting NOCOUNT to ON as for some reason SSIS doesn't always appreciate the rows affected output that SQL Server returns?
Kindest Regards,
Frank Bazan
April 18, 2008 at 5:43 am
I have put lots of CTE's together is a single statement and never had any issues.
The NOCOUNT issue could be the problem.
And since I just had a fresh coffee...
Now that you have a procedure without a temp table, why use a procedure at all? Now you can create a view, or just put the entire SQL into your data flow source.
May 7, 2008 at 12:38 pm
I don't want to put the entire SQL in my source because of SDLC issues. Every time I have to edit an SSIS package, I have to do all sorts of regression testing plus regular testing, plus find all the steps in the package where the change might have been referenced.
It's much easier to do it all as SPs, send the SPs through the SDLC and not have to worry about the package itself (and possible multiple steps) being affected by the change.
Also, it's not a NOCOUNT issue. I finally got the CTE to work without turning on NOCOUNT and even got nested CTEs to work also. Problem is, I can't quite remember what I did to fix it. Which means the next time I have this problem, I'm going to have to go troubleshooting from scratch again. @sigh.
May 7, 2008 at 12:39 pm
BTW, thanks to all who replied to this thread. Your help has been greatly appreciated.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply