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 2008 question Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 3:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 10:23 AM
Points: 25, Visits: 122
I'm working with a DataFlow Task and have the souce and destinations set.

The destination has Field1, Field2, Field3 and a last column called dtEndDate.
The source has Field1, Field2 and Field3.

I want destination's dtEndDate column to be filled in from my package level variable called dtMonthEndDate.

How do I do this? I know I can use a derived column, but I want to avoid this.

I was thinking of specifying a SQL statement instead of the table name in the source. And doing something like:
SELECT @dtMonthEndDate AS dtEndDate, Field1, Field2, Field3 FROM MyTable1.

But it doesn't look like it wants to support something like that. I tried to replace the @dtMonthEndDate with a ? but it gave me an error message.

Any ideas?
Post #1338873
Posted Wednesday, August 1, 2012 4:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:20 PM
Points: 18,064, Visits: 16,099
Any reasons against using the derived column?


Another simple method is to build a stored procedure that does the select query you need. And then call the stored procedure in you package to populate the destination.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1338878
Posted Wednesday, August 1, 2012 4:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 10:23 AM
Points: 25, Visits: 122
I'm thinking the derived column is not as effecient.

If I create a stored procedure, I still have to pass the parameter. And that's the whole point of what I'm trying to do here - figure out how to get parmeters working.

I'm getting the following error message, if that helps:
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.

I'm trying the following SQL for the "SQL Command":
SELECT
CONVERT(DATETIME, ?) AS dtMonthEndDate, Field1, Field2, Field3
FROM dbo.MyTable1
Post #1338884
Posted Wednesday, August 1, 2012 5:13 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:20 PM
Points: 18,064, Visits: 16,099
This article should help with that

http://bisherryli.wordpress.com/2012/01/27/ssis-107-parameterized-query-in-ole-db-data-source-parameter-information-cannot-be-derived-from-sql-statements-with-sub-select-queries/




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1338897
Posted Thursday, August 2, 2012 1:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:24 AM
Points: 5,317, Visits: 12,355
Mister Ken (8/1/2012)
I'm working with a DataFlow Task and have the souce and destinations set.

The destination has Field1, Field2, Field3 and a last column called dtEndDate.
The source has Field1, Field2 and Field3.

I want destination's dtEndDate column to be filled in from my package level variable called dtMonthEndDate.

How do I do this? I know I can use a derived column, but I want to avoid this.

I was thinking of specifying a SQL statement instead of the table name in the source. And doing something like:
SELECT @dtMonthEndDate AS dtEndDate, Field1, Field2, Field3 FROM MyTable1.

But it doesn't look like it wants to support something like that. I tried to replace the @dtMonthEndDate with a ? but it gave me an error message.

Any ideas?


Can you change your Select statement so that it is self-contained? Eg:

select EndOfMonth = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)), F1, F2 etc

Edit--not that I think you will gain much. Derived columns usually perform well, in my experience.



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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1338991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse