Create Proc uspDTSLogSteps

@executionid Varchar(128)

As

--Declare @executionid Varchar(128)

--Set @executionid = '2E1EECB0-AF27-4D9F-85FC-4AF367AE988C'

Select

id,

source,

event,

operator,

computer,

starttime,

message

From

sysdtslog90

Where

executionid = @executionid

Order By

id asc

Hi,

First thing you need to do is set your packages to log to your SQL Server. For more info on this check the following.

http://msdn2.microsoft.com/en-us/library/ms138020.aspx

SSIS will then log event messages to...

Create Proc uspDTSLog As

Select

PackageStart.id,

PackageStart.source,

PackageStart.operator,

PackageStart.computer,

PackageStart.starttime,

PackageEnd.endtime,

DateDiff(s,PackageStart.starttime,PackageEnd.endtime) As duration,

--PackageStart.sourceid,

PackageStart.executionid,

Errors.[message]

From

(Select top 1000

id,

source,

operator,

computer,

sourceid,

executionid,

starttime

From

sysdtslog90

Where

event='PackageStart'

Order By

id desc) As PackageStart

Left Outer Join

(Select

sourceid,

executionid,

endtime

From

sysdtslog90

Where

event='PackageEnd'

) As PackageEnd

On PackageStart.sourceid = PackageEnd.sourceid

And PackageStart.executionid = PackageEnd.executionid

Left Outer Join

(Select

sourceid,

executionid,

[message]

From

sysdtslog90

Where

event='OnError'

) As Errors

On PackageStart.sourceid =...

I've worked it out.....

In your Data Flow check that the correct columns are being passed from the Source to the Data Conversion and to the Destination by double cliking on the connecting arrow...

Well I guess the fact that you are not getting any results in the preview means you won't get any data in the Excel file.

The query works in query analyzer...

Hi,

I've just realised that I was back to front and you are exporting to excel rather than importing from. I have just created simple test package as a test this...

I would still be looking at the data in the source file from the problem. Try opening the text file and navigating to line 36804 where it is failing and...

Hi,

I think Excel text columns come through as Unicode and therefore you would need to put the into Nvarchar fields within SQL.

You can get around this by converting the text...

Hi Steve,

I'm tending to use the Msgbox option already to show variables values as you can see from my example above and I have already updated my connections to OLEDB...

Hi,

It sounds like the problem is in you source data. It might caused by having commas or quotes in the content of your source data which break the definition file...

Hi,

You could achieve this through a simple script task.

Regards

Daniel

Hi,

I found that SP1 for SQL Server hadn't been installed and as if by magic the problems disappeared once it was!

Regards

Daniel

This may help though it is not specific to the error message more the error code.

http://support.microsoft.com/kb/306518

From this KB it appears that there is an issue connecting to the data.

Regards

Daniel

Hi,

I assume the Excel files are all in the same format?

Unfortunately I don't have SQL 2000 on my pc to try this out. However I am expecting the it to...