D'oh... This is what I get for reading forum posts when my caffeine level is low. I completely missed the part where it's doing multiple hits on a view....

Hi,

In a similar sitation, I've created either a temp table or table variable and then used INSERTs rather than unions. As in:

declare @mytable table

(invoiceno numeric,invoicedt datetime,customerid numeric,totout numeric,six numeric,seven...

At the very least, you need to take the fields relating to Skipper and Crew Member out into separate tables, related with SkipperNo and CrewMemberNo.

That assumes you only have one...

You beat me to it... I had just found the lovely fn_varbintohexstr().

So, if I read the XML node into a varbinary, I can then run the function over the varbinary,...

Indeed...

I've got the Base64 -> varbinary sorted... you wouldn't know about the -> Hex conversion would you?

I've been butting my head against this for about a week now, there's a...

Hi Adi,

You confirmed what I thought. I even managed to find the bit in BOL that explains the behaviour. I had looked, but I missed that part.

Anyways, here...

That post did help.

I'm not using the parameter mappings tab at all now - I'm building the SQL statement as an expression with the variable values sent directly to SQL...

Kari -

Your using the expression to build the statement helped me with a problem I was having (http://www.sqlservercentral.com/Forums/Topic921028-148-1.aspx#bm921153). I've bypassed the parameter mapping tab all together and...

You could also put a UNION after your main query, with nulls for the database columns and the hard-coded constants, EG

select name, address, '12345' as zip from mytable

where name='my_name'

UNION ALL

select...

Me again...

I've discovered the problem is that ODBC basically strips the metadata from stored procedures in this case, which means, in this case, if the stored procedure returns a result...

I've fixed this - it had to do with the metadata for the stored procedure being read by the SSIS.

The problem seems to be due to a pair of SET statements appearing before the select:

DECLARE @TODAY_DATE AS VARCHAR(20)

DECLARE @TODAY_DTTM AS DATETIME

SET @TODAY_DATE = CONVERT(VARCHAR,GETDATE(),103)

SET @TODAY_DTTM = CONVERT(DATETIME,@TODAY_DATE)

If I comment...

I copied the files to the server, and I found I need to specify the configuration files in the Job Task as well. If you go into the step...

Me again:

I found the problem and the DTS package is now working.

In the DTS Package, the last Execute SQL task truncated the database log. The DTS then did a...