About me

I work as SQL Server DBA / Developer and BI Consultant in Toronto, Canada.Canada Flag More...
Vidas Matelis picture

MVP Logo
Pyramid Analytics

Search

Pyramid Analytics
blank
ESS

Report Portal
blank

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

  1. Joschko Says:

    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

  2. Darren Gosbell Says:

    Hi Vidas,

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

    eg. /* my comment line */

  3. Vidas Matelis Says:

    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.

  4. Darren Gosbell Says:

    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.

  5. Brian Says:

    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.

  6. Surendra Says:

    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.

  7. Brian Says:

    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

  8. Animesh Pathak Says:

    @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”

  9. Vamshi Says:

    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…

  10. Prasad Says:

    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.

  11. LLL Says:

    Good one Vidas,
    you solved my problem

  12. Krishna Says:

    I cannot imagine how I should thank you for posting microsoft’s comment.

    I spent one whole day and finally found the fix.

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.

Pyramid Analytics