March 19, 2015 at 4:47 am
Does anyone knows why there are no columns shown in OLE DB source component at Data Flow Task in SSIS when using Data Access Mode = SQL command from variable. However if I just put a simple select to the same table there will be columns shown.
March 19, 2015 at 8:28 am
I'm not sure what you mean.
I have an OLE DB source with SQL command from variable and I can see the columns.
Maybe there's an issue with your variable?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 19, 2015 at 10:55 am
Hi Koen,
Good day!
I have seen why it doesn't show the columns at the OLE DB.
It is because the stored procedure being called/executed by OLE DB source
object is in string.
I can't show the exact query here but it looks like this one.
SET @Query = '
SELECT a as ''Name'',
b as ''age''
FROM ''+@table+''
'
EXEC @Query
The resolution I have done is to rewrite the SQL query to a direct one.
I put the exact table name instead of passing a parameter.
And by that, the OLE DB source now shows the columns.
Having a dynamic code is a good one but make sure it will not conflict 😀
At last I solved it.
Cheers! 😀 I can sleep now without questions in mind. :hehe:
By the way, the sql query I actually have is okay and error free.
Its working fine, but it just conflicts when used with OLE DB object. 😀
March 19, 2015 at 12:25 pm
The OLE DB doesn't like stored procedures or dynamic SQL, because it can't determine metadata easily.
If you want to use it in SSIS 2012 and up, you need to use EXEC ... WITH RESULT SETS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 20, 2015 at 3:15 am
yes that's right 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply