OLE Db Source and Variables

Hot Topic

Checkpoint file used by another process
Error while using Stored Procedure
Connecting to a local cube using Server.Connect(strStringConnection) method of AMO
Custom Security Extension
Can not see columns in query "Design View"
POST Method
problems with sync_type 'replication support only'
An error occurred while performing this operation.
Tools to generating database creation scripts.
Dimensions renamed after AS2005 migration

SQL Server

Gartner Reviews Data Integration Tools
Parent Child Dimension
Save data flow task result into specific table in database
Installation of SQL Server 2005 + RS on a vista 64 bits
xp_sendmail
sp_OACreate in SQL Server 2005, Fails with 'Not enough storage is available to complete this operation'
Query issues
Performance problem: SSAS 2005 + ProClarity #2 (Update)
Report printing issue using the browser print control after deployment
Cannot delete users in SQL 2005?

OLE Db Source and Variables


I created a OLE DB Source, and created a sql command text.

The issue is with parameters, I have to use the ' ' to identify.

What if I have multiple variables of the same type scatter around, how can I define that

Somthing like this would be good...


Declare @month
set @month =

select blah,@month where month=@month

or

How could I use the ADO Syntax and just put @month

Thanks,

Mardo



Answer this question

OLE Db Source and Variables

  • explode

    Execuse my ignorance but I only see a OLE DB Data Flow Source. I understand that ADO.NET can used name paramters but I can only seem to use that in Data Flow Destinations.

    Marty



  • rternier

    Hi Mardo,

    OLE DB only supports ' ' style parameters. You can use ADO style named parameters if you define an ADO.NET connection manager. Ensure you set the ConnectionType property of the Exec SQL Task to ADO.NET in order to reference the new connection manager.

    Cheers,

    Nick

    http://nickbarclay.blogspot.com



  • A.F.B

    another solution would be to use variables and expressions to dynamically create the sql statement.

  • Alex-MyRpg

    Guys, my apologies for being a bit misleading.

    The .NET connection manager can be used in conjunction with the Execute SQL task (in the control flow) - when using this type of connection manager you are able to use named parameters. The OLE DB source adapter (in the data flow) is specifically OLE DB i.e. it must use an OLE DB connection manager to access the DB. As Duane posted earlier you can also "use variables and expressions to dynamically create the SQL statement", other than that you will have to use " " as parameter placeholders.

    Cheers,

    Nick

    http://nickbarclay.blogspot.com



  • OLE Db Source and Variables
Answer this question