Vidas Matelis Analysis Services Blog

SSIS: Issue with OLE DB Source component that is based on parametrized SQL Query

September 13th, 2007 by Vidas Matelis

While writing SSIS package to load one of my data warehouse tables, I encountered SSIS problem that took me some time to figure out. I noticed that in the dataflow task one of the OLE DB source components was not returning any data even I know that records were there. So I did some investigation and found what was causing this issue.

I found that when following conditions are met, OLE DB Source component will not return any data:

“OLE DB Source” component is based on the SQL Query
SQL Query contains parameters
SQL Query contains line that starts with comments symbol “–”

Example, this query (database “Adventure Works DW”) works:

SELECT CurrencyKey
FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? AND ?

But this query always returns empty result set:

SELECT CurrencyKey
FROM dbo.DimCurrency
— my comments
WHERE CurrencyKey BETWEEN ? AND ?

Here are quick steps to reproduce this issue:

In Adventure Works DW database create new table based on the script

IF OBJECT_ID(‘dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
CREATE TABLE tmpKey (CurrencyKey int NOT NULL);

This will be our destination table and you can delete this table after this test.

Start BIDS and create new SSIS project and then SSIS package.
Add OLE DB connection to Adventure Works DW database. Name it DB for simplicity.
Add “Execute SQL” task to control flow. Change connection to DB and SQL Statement to:

IF OBJECT_ID(‘dbo.tmpKey’) IS NOT NULL DROP TABLE tmpKey;
CREATE TABLE tmpKey (CurrencyKey int NOT NULL);

This way we will be able to run package multiple times.

Add two variables to package:
Name: MinID Type: Int32 value 1
Name: MaxID Type: Int32 value 2
Add data flow task and connect it to go after “Execute SQL” task.
Double click on data flow task to go into data flow area.
Add “OLE DB source” and “OLE DB destination” components. Make connection from “OLE DB Source” to “OLE DB Destination”
Change “OLE DB source” properties:
Connection: DB
Data Access Mode: SQL Command
SQL Command text:
SELECT CurrencyKey
FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? and ?
For “OLE DB Source” component click on “Parameters” button and map parameters:
Parameter0 User::MinID
Parameter1 User::MaxID
Change “OLE DB Destination” properties
Connection: DB
Data access mode: “Table or view – fast load”
Name of the table or the view: “dbo.tmpKey”
Click on Mappings and map “CurrencyKey” to “CurrencyKey”
Add data viewer to see records moving between “OLE DB Source” and “OLE DB Destination”
Save and execute package. You will see 2 rows moved between source and destination. That is correct behaviour.
Now change “OLE DB Source” SQL command text to:
SELECT CurrencyKey
FROM dbo.DimCurrency
— my comment line
WHERE CurrencyKey BETWEEN ? and ?
Save and execute package. No data will be moved. That I believe is a bug.

I submitted this issue to Microsoft connect website:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=298046

Of course, workaround for this problem is to remove line that starts with comments.

Mar 14th, 2008 update. Microsoft reply:

The problem that you have described is an issue with the SQL Server Native Client provider. This problem occurs with a parameterized SQL query using in SSIS components such as the OLE DB Source, when the query contains a comment line that begins with the comment characters “–“. The OLE DB Source does not parse the SQL statement (because it must support the varying SQL syntax of various data sources) and cannot catch the problem. The only solution is to omit comments from parameterized SQL statements used in the OLE DB Source.

Posted in SQL Server, SSIS | 12 Comments »

12 Responses

Joschko Says:
September 14th, 2007 at 2:34 am
Hello Vidas,
this is very helpful for me.
Just a few days ago i had the same issue.
I moved all OLEDB Tasks to ADO.NET.
Now I know why.
Best regards
Jörg
Darren Gosbell Says:
September 20th, 2007 at 1:15 am
Hi Vidas,

I’m curious to know if the same issue occurs if you use the block comment syntax?

eg. /* my comment line */
Vidas Matelis Says:
September 20th, 2007 at 6:07 am
Hi Darren,

Tested this as you suggested with block comments:
SELECT CurrencyKey
FROM dbo.DimCurrency
/* my comment line */
WHERE CurrencyKey BETWEEN ? and ?

This works no problem, so that is good to know.
Darren Gosbell Says:
September 20th, 2007 at 8:52 pm
Sounds suspiciously like the OLEDB Task is stripping out whitespace (including line breaks) before it executes. It’s strange that it does not throw an error.
Brian Says:
June 16th, 2008 at 2:28 am
I was stuck with connectvity problem from SSIS when executing SQL task that connects to Oracle database.
i keep getting the error

I’m running on SQLserver2005 on 64bit Win 2003 SP2.
I have one SSIS package which reads and writes data from (and to) Oracle database.
I tried with OLEDB and ODBC, I can connect and sucessfully test (while creating the connection) from BIS, but
when I attempt to execute the package in BIS, I get the error

[Execute SQL Task] Error: Failed to acquire connection “dw.world.cud”. Connection may not be configured correctly or you may not have the right permissions on this connection.

I’ve read somewhere,that its compatibility of 64bit oracle driver problem , while oracle donot have 64bit, BIS try to connect thru 64bit. So, later on I tried using dtsrun.exe in \program Files x86 \… however this time
get below error.

C:\ProgramFilesx86\Microsoft SQL Server\80\Tools\Binn>DTSRun /F D:\
N\PROJECTS\usage\USAGE_DATA.dtsx
DTSRun: Loading…

Error: -2147220220 (80040504); Provider Error: 0 (0)
Error string: The specified file is not a Package Storage File.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 704

Can someone advise , how can I connect from 64bit machine via SSIS to an remote oracle instance ?.

note : The same package works fine in 34 bit environment & I can connect to oracle, however our actual servers are 64 bit.
Surendra Says:
June 23rd, 2008 at 1:40 am
SSIS OLE DB Source returns empty set when it is based on the SQL Query
when we using query
select…
from..
where Changecontrol > cast(cast(? as bigint) as binary(8))
what is the issue ?
we are getting the result for the same query when we running in management studio.
Brian Says:
June 24th, 2008 at 10:38 am
OLEDB continue to haunt :-(

All, I’m trying is to just read from a excel and write into a Oracle table.

I’ve a simple package that does that, when I attempt to execute on 32 bit machine. it loads without any problem.

When I take the same package and execute in 64bit platform, it simply fails with message

Error:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager “DestinationConnectionOLEDB” failed with error code 0xC0202009.

I’m just curious is someone EVER successfully loaded data into Oracle using OLEDB ?

Thanks
Brian
Animesh Pathak Says:
February 13th, 2009 at 9:47 am
@Brian

Try running the package using short name. I think it is an OLEDB issue for 64 bit as oracle considers parenthesis as a key word.

C:\Progra~2\Microsoft SQL Server\80\Tools\Binn>DTSRun /F “D:\
N\PROJECTS\usage\USAGE_DATA.dtsx”
Vamshi Says:
May 20th, 2009 at 4:23 pm
Hi Surendra

I had a similar issue where in

select…
from..
where Changecontrol > cast(cast(? as bigint) as binary(8))

doesnt return any records…

This is because,
when you assign MAX(TimeStampColumnValue) to your parameter, it will assign it as a Byte[].

No matter you CAST or CONVERT that to BIGINT and/or VARCHAR or BINARY or VARBINARY it will still remain as Byte[]

Here is what i did to resolve this issue

I have declared a variable in SSIS package

LastChangeControl as Object (System.Object) in SSIS

in the ExecuteSQLTask, I have this query
“SELECT MAX(ISNULL(ChangeControl)) FROM xxxTableName” and assigned to the SSIS variable

Now, my SSIS variable LastChangeControl is a Byte[] with appropriate value distributed in Bytes Array

in the OLE DB Source I have selected a Statement/Query and added SQL Query like this

SELECT * FROM yyyTableName WHERE ChangeControl > ?

If you can run the Profiler for the above statement (while executing from SSIS), you can see ? coming as a Timestamp and not as VARCHAR/BIGINT/BINARY/VARBINARY

So, when you decalre your SSIS variable as Object you dont need explicit CAST or CONVERT functions.

This should work, infact it worked for me…
Prasad Says:
March 26th, 2010 at 10:59 am
Hi,
In my pacakge am using the OLEDB Source(Oracle data source),In OLEDB source Editor,i selcted the data access as SQL COMMAND optin,and my query is “select col1,col2 from table where col1=?”.
When i click on PARAMETERS button it is showing the error like as follows:
……………………………………….
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the “SQL command from variable” access mode, in which the entire SQL command is stored in a variable. (Microsoft Visual Studio)
…………………………………..
Please help me.
Thanks in advance.
LLL Says:
June 23rd, 2010 at 8:01 pm
Good one Vidas,
you solved my problem
Krishna Says:
March 10th, 2011 at 3:47 pm
I cannot imagine how I should thank you for posting microsoft’s comment.

I spent one whole day and finally found the fix.

About me

Links

Categories

Search

Recent Posts

Recent Comments

SSIS: Issue with OLE DB Source component that is based on parametrized SQL Query

12 Responses