Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

Problem with Replication

I keep getting an error when I try to set up a merge replication...
Cannot insert duplicate key row in object 'TRDFIL' with unique index
'ITRDFIL0'.
(Source: CMTSQL2 (Data source); Error number: 2601)
I can't seem to figure out how to prevent this from happening, any ideas on
what is not correct?
ThanksHow did you apply your snapshot?
Normally you get this error for one of two reasons.
1) You applied the snapshot manually, either by a restoring a backup, or a
data transfer process (DTS, bcp, etc). Your snapshot is out of sync, and you
are performaning an insert, update operation for which the data already
exists on the Subscriber.
2) Someone or some process has already entered data in the Subscriber, and
the replication process is trying to add the same row.
Use EM, expand your server, expand Replication Monitor, expand the
Publishers Folder, expand your publisher, right click on the publication,
select Validate Subscriptions.
After this process has run, double click on your distribution agent, select
session details to view the results of the data validation process.
"Atley" <atley_1@.homtmail.com> wrote in message
news:eCBH6bVCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> I keep getting an error when I try to set up a merge replication...
> Cannot insert duplicate key row in object 'TRDFIL' with unique index
> 'ITRDFIL0'.
> (Source: CMTSQL2 (Data source); Error number: 2601)
> I can't seem to figure out how to prevent this from happening, any ideas
on
> what is not correct?
> Thanks
>

problem with query

Hello group,
I have the following issue:
select convert(varchar, GETDATE(), 105)
--
30-10-2007
(1 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda
--
28-10-2007
30-10-2007
31-10-2007
01-11-2007
(4 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
--
30-10-2007
31-10-2007
(2 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
--
(0 row(s) affected)
What is the problem with my last query ? It should return:
30-10-2007
31-10-2007
01-11-2007
Any help will be REALLY appreciated.
greetings,
hansHi Hans,
The problem is that in your WHERE clause you are comparing the dates as
character strings. As such the end period in your BETWEEN becomes
'05-11-2007'. That sorts alphabetically before any of the other dates and
your range does not return any results.
One of your queries returns the desired results but that is just by
coincidence (because in that case the character representation of the end
date is higher in order).
The correct way to return a range of dates is to compare dates in their
native format, not to convert them. To trim the time portion you can use
different techniques, one is via using the DATEADD and DATEDIFF functions.
Here is a query that will return the desired results:
SELECT CONVERT(VARCHAR, ini_fecha_actividad, 105)
FROM Agenda
WHERE ini_fecha_actividad >= DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP), '20010101')
AND ini_fecha_actividad < DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP) + 6, '20010101');
Note that I changed the math and added 6 days to the upper boundary to
include the results dates for the next 5 days.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hans, you should try this approach which compares dates with datetypes.
declare @.d1 smalldatetime
set @.d1 = '11-01-2007' -- Nov 1st
select convert(varchar, @.d1, 105)
where @.d1 between GETDATE() and dateadd(day,5,GETDATE())
answer: 01-11-2007
I removed the references to a table but you get the format - works the same.
--
James Chacata
jameschac@.hotmail.com
"Hans" wrote:
> Hello group,
> I have the following issue:
> select convert(varchar, GETDATE(), 105)
> --
> 30-10-2007
> (1 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda
> --
> 28-10-2007
> 30-10-2007
> 31-10-2007
> 01-11-2007
> (4 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
> --
> 30-10-2007
> 31-10-2007
> (2 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
> --
> (0 row(s) affected)
>
> What is the problem with my last query ? It should return:
> 30-10-2007
> 31-10-2007
> 01-11-2007
>
> Any help will be REALLY appreciated.
> greetings,
> hans
>