January 31, 2013 at 6:57 am
I have loaded a sql query into a variable and selected the "SQL Command from variable" option within my OLE DB Source Editor. My variable value is called "TableLoadQuery".
Below is a code snippet of TableLoadQuery and how I have placed the variables within the SQL, but I keep getting "Invalid column name ' + @[User::..." errors. Has anyone ever been able to do this? Do you have any ideas around it? And yes, Utlity is supposed to be spelled like that.
DECLARE @mydate DATETIME, @dtServiceMonthStart datetime, @dtServiceMonthEnd datetime, @dtArchiveDate datetime, @intRedistributeDays int, @dtISTADateStart datetime
, @strDUNS varchar(50)
SELECT @mydate = GETDATE()
,@dtServiceMonthStart = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101))
, @dtServiceMonthEnd = (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101))
, @dtArchiveDate = GETDATE()
, @intRedistributeDays = " + @[User::RedistributeDays] + "
, @dtISTADateStart = " + @[User::ISTAStartDate] + "
, @strDUNS = " + @[User::UtlityID] + "
--SET @dtISTADateStart = ISNULL(@dtISTADateStart, '2009-12-15')
SET @intRedistributeDays = @intRedistributeDays + 1
SET @dtServiceMonthStart = CASE
WHEN @intRedistributeDays IS NULL THEN @dtServiceMonthStart
WHEN DATEADD(dd, -1 * @intRedistributeDays, GETDATE()) < @dtServiceMonthStart THEN DATEADD(dd, -1 * @intRedistributeDays, GETDATE())
ELSE @dtServiceMonthStart
END
SELECT
m.MeterID AS ISTA_METER_ID
, m.MeterNo AS METER_NUMBER
, p.PremNo ..........
January 31, 2013 at 7:08 am
Hello,
I'd say that the simplest way to handle this would be to add a script task before the "OLE DB Source Editor" and assemble the SQL command there.
Regards,
PMWar
PMWar
January 31, 2013 at 11:43 am
try removing "@" infront of SSIS variables in the query, appropriate casting, also verify final query in the SQL command variable with some dummy data in other ssis variables
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply