Executable or Way for User to Launch an SQL Package?

I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy

If its through a web or VB type app, let the web app execute a stored procedure called by the app.

Cheers,

SG.|||Hey,
from a vb app or vb script you can do this function:

Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

End Sub

excluding a package from the build process

There is some way to exclude a package from the build process of a SSIS project ?

Cosimo

cosimog wrote:

There is some way to exclude a package from the build process of a SSIS project ?

Cosimo

I don't think so. it would be nice though wouldn't it? Try submitting the request at http://connect.microsoft.com/sqlserver/feedback

-Jamie

excessive sending package from the sqlserver

The users reported excessive sending package from the sqlserver, we intented down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?
Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

excessive sending package from the sqlserver

The users reported excessive sending package from the sqlserver, we intented
down the services but we can't
The server administrator rebooted the server and the network was stable
What do you think about it?Sounds suspiciously like a Slammer infection. I'd suggest you take a
look here http://www.microsoft.com/security/incident/slammer.mspx and
make sure your servers are patched correctly.
g.
http://www.sqlskunkworks.com
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

excessive dtexec logging post SP2

Since the other related topic is closed/answered...

The Short version:

SQL is now logging too much info with every package. The volume of the new "User: Diagnostic" event has caused some packages to fail and the command-line exclusion option appears to have no effect on the events logged to the SQL provider. Is this a bug in dtexec or am I using the wrong syntax to exclude log entries? I don't want to modify all of my SSIS packages...

More Info:

SQL SP2 introduced new logging events, most of which appear to get logged by default. So far, none of our packages have used any sort of explicit logging configuration; it's all been set at the command line using a syntax like shown below:

dtexec.exe /FILE "D:\SSIS Packages\MyApp\Vendors.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REP E;Diagnostic /LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"MyDBConnName"

This does appear to correctly limit what gets logged to the console (and thereby the SQL Agent's job step log), but has no effect on what's logged to the database. Normally, I'd use /REP EWDCI, but I was attempting to limit the log entries to Errors only.

I first came across this error when a package failed, but it only logged the following to the console with nothing in sysdtslog90 (while not the "latest/greatest" server, this is a relatively low-utilized quad 2.8ghz xeon ProLiant DL580 G2):

Error: 2007-06-21 06:01:30.45
Code: 0xC0202009
Source: MYPACKAGENAME Log provider "{0C3CBE9B-D828-41C2-98D2-99BA498B314A}"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Connection is busy with results for another command".
End Error
Error: 2007-06-21 06:01:30.46
Code: 0xC0014010
Source: MYPACKAGESTEP Load
Description: The SSIS logging provider "{0C3CBE9B-D828-41C2-98D2-99BA498B314A}" failed with error code 0xC0202009 ((null)). This indicates a logging error attributable to the specified log provider.
End Error

I changed this one package to only log OnError events, but I'd rather not have to change every package to do the same, plus I'd like the ability to easily turn on verbose or any other logging level when needed.

At this point, fine-grained control of logging which is initiated external to the package itself, is basically only available programmatically. External logging can be accomplish by by implementing a logger, an event listener, by hooking/redirecting the byte stream produced by dtexec.exe, or by programmatically configuring the LoggingOptions class on the package object. Within the package itself is a differrent story, but it would seem you're looking for externalization of logging control.

dtexec's "/reporting" command line switch relates to console events and the "/consolelog" command line switch relates to fields which are sent to the console for a given event. To observe the applicability of the two switches, run dtexecui.exe, a gui based dtexec command line builder. Notice under the reporting node, the selectable events are listed as "console events".

EXCEPTION_ACCESS_VIOLATION within SQL Server when running SSIS package

Hi, I'm not sure if this is the right forum, so please point me in the right direction if it isn't.

I'm getting an EXCEPTION_ACCESS_VIOLATION and a minidump in the SQL server logs when using the OLE DB Provider for SQL Server.

The error occurs when I run an SSIS package non-interactively (using Package.Execute). The odd thing is that the package works fine when I run it from within Visual Studio. Also, this same bit of code was working fine a couple of weeks ago (it's part of my unit tests, it just started failing when I added a new, unrelated SSIS package to the project). The error occurs during the validation phase.

Here's the first part of the stack dump, which just shows that the provider is executing a very simple statement (SELECT * FROM [table-name]):

Code Snippet

* BEGIN STACK DUMP:
* 05/14/07 10:42:34 spid 56
*
*
* Exception Address = 01CBBF90 Module(sqlservr+00CBBF90)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 0000001C
* Input Buffer 108 bytes -
* ?? & ?> ff ff 02 00 00 00 00 01 26 04 00 00 00 e7 3e 00 09 04
* 2> s e l e c t 00 01 32 3e 00 73 00 65 00 6c 00 65 00 63 00 74 00 20
* * f r o m [ d 00 2a 00 20 00 66 00 72 00 6f 00 6d 00 20 00 5b 00 64
* b o ] . [ P x p T 00 62 00 6f 00 5d 00 2e 00 5b 00 50 00 78 00 70 00 54
* a r g e t ] & 00 61 00 72 00 67 00 65 00 74 00 5d 00 00 01 26 04 04
* & & 02 80 03 00 00 01 26 04 04 04 80 04 00 00 01 26 04 00
*

I presume this is something for the SQL Server team; how should I go about getting this resolved?

Cheers Rich

To report this you need to open a ticket with Microsoft PSS. Your support options can be found here https://support.microsoft.com/common/international.aspx?rdpath=1&gprid=2855.

Exception from HRESULT: 0xC0048004

Hi, there;
I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table.

Does anybody how to resolve it?

Thanks

Qiuwei wrote:

Hi, there;
I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table.

Does anybody how to resolve it?

Thanks

its nothing to do with indexes in your tables. It is most likely referring to an attempt to reference an item in a collection within your package. It suggests that your package is corrupt.

Do you get the same error when you execute the package using dtexec.exe?

-Jamie

excel source with optional columns

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA

I recently solved this problem using a dynamically built select statement. Is it always just 1 column that's missing or do you need to load a dynamic number of columns? If it's a truly dynamic then the algorithm is a little more complex...|||

Thanks for your response. Request you tell me more aboout it.

I did the whole thing in BIDS in a SSIS project, using a ForEach container, a Excel Source and an OleDB destination. I was hoping to achieve my objectives with these objects and their settings :-).

|||I used a For Each Loop and then a For Loop to solve this problem.

The first For Each Loop iterates threw the columns names in the spreadsheet. It contains a script component that counts the columns storing the result in a variable. There might be a more efficient way to count columns but I couldn't figure out how.

The second For Loop container uses this counter variable to select and load each column one at a time. It contains 2 components; a script component that builds a select statement and a data flow task that actually moves the data using the select statement.

Here is the script code that dynamically builds each select statement:

Public Sub Main()
Dim SelectCommand As String
Dim WorksheetName As String
Dim ColumnLoopIndex As Integer
WorksheetName = Dts.Variables("WorksheetName").Value.ToString
ColumnLoopIndex = CInt(Dts.Variables("ColumnLoopIndex").Value)
SelectCommand = "Select F" & ColumnLoopIndex.ToString & " AS CurrentColumn from [" & WorksheetName & "]"
Dts.Variables("SelectCommand").Value = SelectCommand
Dts.TaskResult = Dts.Results.Success
End Sub

Please note: Depending on your data and how dynamic you want the

package to be you could skip the second For Loop and build a single select

statement that loads all of the columns. In this case your dynamically built select

statement would contain return fields like "SELECT F1, F2, NULL AS F3, NULL AS F4

FROM [myworksheetname]" to account for missing F3 and F4 columns.

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

excel source not reading newly/appended rows

I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.

Any idea why ?

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.
|||

JayH wrote:

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.

No hidden rows... I m experiencing this with couple of packages having excel as source.

|||Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?|||

Ted Lee - MSFT wrote:

Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?

Yes it works if I do that...

|||

My problem is solved. Under name of the excel sheet in excel source editor two items appear one with original name of the excel file and another one with $ sign appended to excel file name. If I select the one with $ sign it worked for me.

Any idea what both items mean?

Thanks

|||

See the following in Books Online for the Excel Source:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_0evalplan/html/e66349f3-b1b8-4763-89b7-7803541a4d62.htm

"In Excel, a worksheet or range is the equivalent of a table or view. The list of available tables in the Excel Source and Destination editors displays existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange). For more information, see the Usage Considerations section."

|||

Thanks Ted...

Excel Source from Variables and data type Issue

Hi

I am after some help with the following issue

I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.

Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.

Any help is gratefully aprreciated.

DT_WSTR and DTNTEXT are different types, so SSIS detects this as a metadata change. It's based on what the Excel provider reports back to SSIS.

Someone ran into to this a few months ago, and I think they ended up having to use two different data flows, one for DT_WSTR and one using DT_NTEXT.

|||

hi

Thanks for the reply.

I have it kind of working using your suggestion here. I now have 2 data flow tasks, the first is for the troublesome column as a DT_WSTR (the most common for the files imported) and an on fail constraint to a NTEXT. In theory it works fine however because the first component fails when it is an NTEXT the whole package fails because it is within a foreach loop.

as far as I can see the only solution would be to either increae the Maximum number of errors on the foreach loop which will cause a problem if something else outside of these 2 components fails or to change the error handling on the dataflow task. I cannot ignore the error on truncate in the dataflow task as it will suceed but will import only the first 255 characters using the DT_WSTR flow. If I cange the onerror erro output it still appears to record the error against the error count so the max error count is reached.

Any suggestions would be greatly received

Thanks again

|||

Could you use an Execute SQL or Script task to query the metadata for the Excel sheet ahead of time, and redirect to the appropriate task? This link has more information on using GetOleDbSchemaTable to get this type of information.

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

Excel Source from Variables and data type Issue

Hi

I am after some help with the following issue

I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.

Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.

Any help is gratefully aprreciated.

DT_WSTR and DTNTEXT are different types, so SSIS detects this as a metadata change. It's based on what the Excel provider reports back to SSIS.

Someone ran into to this a few months ago, and I think they ended up having to use two different data flows, one for DT_WSTR and one using DT_NTEXT.

|||

hi

Thanks for the reply.

I have it kind of working using your suggestion here. I now have 2 data flow tasks, the first is for the troublesome column as a DT_WSTR (the most common for the files imported) and an on fail constraint to a NTEXT. In theory it works fine however because the first component fails when it is an NTEXT the whole package fails because it is within a foreach loop.

as far as I can see the only solution would be to either increae the Maximum number of errors on the foreach loop which will cause a problem if something else outside of these 2 components fails or to change the error handling on the dataflow task. I cannot ignore the error on truncate in the dataflow task as it will suceed but will import only the first 255 characters using the DT_WSTR flow. If I cange the onerror erro output it still appears to record the error against the error count so the max error count is reached.

Any suggestions would be greatly received

Thanks again

|||

Could you use an Execute SQL or Script task to query the metadata for the Excel sheet ahead of time, and redirect to the appropriate task? This link has more information on using GetOleDbSchemaTable to get this type of information.

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

excel problem

hi I have an excel sheet with no header row.
so I used DTS package to transfer data to the table,
however, transform data task think my first row is the header column,
as the result, I can never transfer my first row data to the table.
is there a way to do this? maybe I have to use activex?
regardsI'm trying to leave work, but
HDR=Yes;IMEX=1
is something I remember about hte header row.
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
> hi I have an excel sheet with no header row.
> so I used DTS package to transfer data to the table,
> however, transform data task think my first row is the header column,
> as the result, I can never transfer my first row data to the table.
> is there a way to do this? maybe I have to use activex?
> regards
>|||where do I put this code?
I tried to put the code in activex,
nothing happen. still same problem..
thanks for helping
"sloan" wrote:

> I'm trying to leave work, but
>
> HDR=Yes;IMEX=1
> is something I remember about hte header row.
>
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:AAAA9CB5-206D-4721-B033-09EBD825100F@.microsoft.com...
>
>|||Refer this link
http://www.sqldts.com/default.aspx?254
Regards
Sudarshan Selvaraja
"Kevin" wrote:
> where do I put this code?
> I tried to put the code in activex,
> nothing happen. still same problem..
> thanks for helping
>
> "sloan" wrote:
>

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

Excel Import Error

Hello,

I am trying out a simple SSIS package to import data from an xls into a table. For this I am using an Excel Source and an OLEDB Destination.
One of the fields is called Notes. In the Excel Source, I have specified the Notes columns as Unicode string [DT_WSTR]. This maps to a SQL Table column which is nvarchar(max)
The error messages that I get are :

[Excel Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Source [1]] Error: There was an error with output column "Notes" (272) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".

[Excel Source [1]] Error: The "output column "Notes" (272)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Notes" (272)" specifies failure on error. An error occurred on the specified object of the specified component.

Could anyone please help me with this.

regards,

Satya

Hi Satya,

I'm no expert so this is a best guess as to what might be happening. It sounds as though there may be rows in the excel file where the Notes filed contains data which isn't compatible with the DT_WSTR data type. What i would be inclined to do first is the following:

Right click on the Excel data source object and bring up the advanced edit options. Select the Input and Output Properties tab and then the output columns folder under inputs and outputs treeview. You should (if i'm not mistaken) find a property for ErrorRowDisposition. Change this value to RD_IgnoreFailure.

If you now save and run the package does it execute as expected. If there are indeed rows which have caused problems these should have been ignored by the transformation.

Let me know if this helps.

Cheers,

Grant|||

Great workaround, I had the similar problem, but is there any way to avoid the error itself?

Thanks

Atul

|||You should use a data conversion to convert the strings to unicode