SSIS 2008 question

  • 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?

  • 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

  • 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

  • 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[/url]
    Learn Extended Events

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply