October 28, 2013 at 8:46 am
Hi, I'm hoping to get help with a problem I've been struggling with for several months. I have inherited a set of ssis packages. I need to modify one of these packages. The existing package has a control flow which houses an execute SQL Task step that goes out to the database and pulls back a value for the date(s) the package needs to execute for. The scope of the variables is set to the package level. This step then connects to a sequence container that houses a data flow task. My problem lies in the first step of the data flow. I have an ADO Net Source component that uses a SQL Command which needs the variable values to get a set of data between a start date and end date. The variables are set up in the package as shown in the attachment (and below), and I need specifically to use FromDate and ToDate parameters:
---
Name Scope Data Type Value
FromDate LoadDW Datetime 5/9/2013 12:09 PM
PrevDate LoadDW Datetime 4/30/2013 1:11 PM
RecordCount LoadDW Int32 0
RowCount LoadDW Int32 0
ToDate LoadDW DateTime 5/9/2013 12:09 PM
--
When I open the sql command in the ADO Net Source component of the data flow step I need to modify, the section that uses the FromDate and ToDate paramenters looks like this:
....and j.INVOICEDATE between cast
('5/9/2013 12:09:00 PM' as date) and cast(' 5/9/2013 12:09:00 PM' as date)
When I try to replace these data values with question marks such as ....and j.INVOICEDATE between cast (? as date) and cast( ?as date)......
then click ok I get an incorrect syntax near ? error message.
When I try to insert the names, such as: ....and j.INVOICEDATE between cast(FromDate as date) and cast(ToDate as date).....
I get an error message saying incorrect column name 'FromDate', Invalid column name 'ToDate'
If I leave the values alone, then I only get back the data that has an invoice date of 5/9/2013 regardless of what the value of variables that have been passed to it is.
I've read all types of posts on this, and everything I've read seems to point to the use of the ? to point to a variable. Is there something different that needs to be done when using the ADO Net source as opposed to an OLE DB Source?
Please help!
November 19, 2013 at 11:55 am
No one has any insight on this?
November 19, 2013 at 11:59 pm
Out of interest, can you briefly explain why you are using ADO rather than OLE DB?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 20, 2013 at 6:19 am
No, as I mentioned, I inherited the packages and am an SSIS newbie. I'm just trying to add some functionality to an existing package that I am now doing manually.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply