SSIS 2008 question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » SSIS 2008 question

SSIS 2008 question

Rate Topic

Display Mode

Topic Options

Author

Message

Mister Ken

Posted Wednesday, August 01, 2012 3:52 PM

SSC 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

SQLRNNR

Posted Wednesday, August 01, 2012 4:09 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443

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 2008

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

Mister Ken

Posted Wednesday, August 01, 2012 4:24 PM

SSC 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

SQLRNNR

Posted Wednesday, August 01, 2012 5:13 PM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:33 PM
Points: 18,858, Visits: 12,443

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/

Post #1338897

Phil Parkin

Posted Thursday, August 02, 2012 1:47 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 4,328, Visits: 9,666

Mister Ken (8/1/2012)

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1338991

« Prev Topic | Next Topic »

Permissions