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 01, 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 01, 2012 4:09 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1338878
Posted Wednesday, August 01, 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 01, 2012 5:13 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:30 PM
Points: 20,467, Visits: 14,104
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1338897
Posted Thursday, August 02, 2012 1:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:25 AM
Points: 4,828, Visits: 11,184
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.
Post #1338991
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse