August 1, 2012 at 3:52 pm
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?
August 1, 2012 at 4:09 pm
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[/url]
Learn Extended Events
August 1, 2012 at 4:24 pm
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
August 1, 2012 at 5:13 pm
This article should help with that
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 2, 2012 at 1:47 am
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.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply