Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS parameterized package problem Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:39 PM
Points: 18, Visits: 145
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!


  Post Attachments 
SSIS Error.docx (10 views, 117.56 KB)
Post #1508927
Posted Tuesday, November 19, 2013 11:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:39 PM
Points: 18, Visits: 145
No one has any insight on this?
Post #1515708
Posted Tuesday, November 19, 2013 11:59 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
Out of interest, can you briefly explain why you are using ADO rather than OLE DB?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1515900
Posted Wednesday, November 20, 2013 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:39 PM
Points: 18, Visits: 145
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.
Post #1516010
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse