Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.

Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.

Exchanging a "?" with NULL value

Hi SQL Champs!

I have a text file source where some data are an question mark (?). Importing this to SQL serever, I want to exchange these (?) with an NULL -value.

How do I do this most easy? Do I need a another tmptable first?

Many thaks

kurlan

While importing this you can use the replace function to replace these values on the fly.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Rule!

Thanks

Exception from HRESULT: 0xC0202022

I created a packsge, set up a connection and the connection is tested OK. Then I created a OLE DB Source, gave the created connection and a table. When I click "Preview" button, this is what I got:

Error at DTStask_DTSDataPumpTask_3(OLE DB source[1]): An error occured due to no connection. A data connection is required when requesting metadata.

Additional informatin:

Exception from HRESULT 0xC0202022: (Microsoft.Sqlserver.DTSPipelineWrap)

Please tell me what I did wrong? or have I broken something?

Have you by any chance turned on the "Work Offline" option in the meantime?

ExcelMDX assembly source property is blank

Hi, We're using SQL Server 2005 SP5. Excel 2005 was recently installed on the server so we could use Excel functions in MDX. However, we still get the function does not exist. For example: Query (1, 20) The '[Excel].[Ceiling]' function does not exist. VBA functions work fine.

When looking at the properties of the ExcelMDX Assembly (via SQL Server Mgmt Studio) the Source property is blank.

It just appears that Excel is not registered with Analysis Services. I've searched everywhere for an answer and found a few others describing the same problem but no suggested solutions. Any help would be appreciated.

Thanks,

Ed

I don't have any documentation on this, but I believe that for COM based assemblies that the source property is meant to be blank. The way COM activation works, it does not make sense to load COM assembly source into SSAS. Where as .Net source can be re-assembled into a byte stream and executed directly.

The source property is blank on my laptop and the Excel functions work fine.

Does Excel run OK on the server? It sounds like it might be an issue with the COM registration.

Another option might be that, if you have someone with .Net skills, you might find that implementing your own .Net stored proc might be a faster solution. Generally speaking there is less marshalling overhead with COM assemblies than .Net ones, but the Excel calls have to go cross process which can have a significant performance impact.

Excel Transformation Problems

Hi guys,

I have encountered inconsistencies using the excel data source. When I try to transform some of the fields in excel, though it has values, it sometimes returns null. For example, I have this column below:

X
--

50

30
20

70
60

Sometimes it works, meaning I can get all the values, sometimes I cant, which means it returns all null. Anybody encountered this issue before?

By the way, it only happens if there is some empty values within the working cell.

Thanks.

Cheers,

Ryan Tan
Your column contains what the driver has deemed to be a mix of numeric and text values. It has ruled in favor of the majority (numeric?) and returns other cell values as null. This is an age-old issue with the Excel driver. Please see the BOL topic on the Excel Source for workarounds for common issues.

I can email you a more detailed known issues document (than I was able to include in BOL) tomorrow if you'll remind me, please, at douglasl@.microsoft.com.

-Doug
|||Have a look at the article below. This explains the age-old issue with Excel.

http://www.sqldts.com/default.aspx?254

Thanks

Sutha

Excel task has no output columns

I created an Excel Source and used a query to get the data,i.e

SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]

The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?

Here is some information that may answer your question:

1. In the Excel Source Editor, navigate to the Columns page.

You can modify the output column names that are listed in the External Column/Output Column table by clicking in the field and typing a new name.|||

Hi,

The problem is there's no output columns listed, and you cannot manually add them. The problem is when I specify a query instead of an entire worksheet, the External Column meta-data is not added like it is when you select a worksheet by name.

Dave

|||

Hi,

Have you tried naming the columns in your excel source as some meaningful names and then try to connect your excel source. It will give you all the names ,after that you can select the ones you need and dont tick the checkboxes of the ones you dont need.

Regards,

Vikram Kansal

Excel Source: not to import blank rows

I am trying to load data from an excel file, how do I use SSIS so that it won't import blank rows in the source excel file?

Thanks in advance!

As with other databases, you'll need to use a WHERE clause to exclude rows that you don't want. There is no such option available on the driver, the connection manager, or the Excel source or destination.

-Doug

|||

The easiest way ist to load all rows from thr excel file and then use in the DataFlow a Conditional Split transformation to split the blank rows.

Your condition may be ISNULL(xxxxxx).

|||

Thank both of you very much. Condition Split works.

I have another question:

SSIS is importing data from an EXCEL source file, which is on a password protected web site, like: http://www.abc.com/myexcel.xls

I always get validation error: Excel Source[649], The AcquireConnection method call to the connection manager "ExcelSource" failed with error code 0xC0202009. Please help!

Thanks again.

|||

If you mean a password-protected Excel file, the driver simply cannot open that.

If you're talking about a Web site that requires a login, the Excel Source certainly can't handle this, but I'm not certain what the solution is. One option might be to use a Script task that logged in and copied the file locally first...

-Doug

|||

Thanks for your reply, Doug.

Yes, what I meant is, the web site requires a login to access the excel file. Script task is an option, but any other suggestions is stilly highly appreciated.

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 numeric/string data problem

i've been reading some problems with excel source data being force as

numeric type when there are string/numeric type in the data, but adding

IMEX=1 to the extended properties will fix this problem...this is true

but not in my case...

say my excel file have about 40 rows..if row 1-39 in column A are all

NULL and row 40 has a string in it, the string in row 40 will not be

converted and the excel source is forcing this column A data type to be

numeric..having IMEX=1 in there does not work..however..if i add a

string anywhere in row 1-8 in column A, the the string in row 40 will

be converted because the external data type now is a string..

anyone know how to solve this problem?solved this problem by going into the excel file and format first first 8 rows as text|||

An alternative is to change the datatype for the column using the "Show Advanced Editor" menu on the Excel Data Source. Select the "Input and Output Properties" tab, expand the Excel Source Output and the the "Output Columns" (Note: not the External Columns). Now you can select change the DataType property of some \ all columns.

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 Source dt_ntext problems

Hi:

I import data from multiple excel files into SQL DB. I have trouble with fields that could contain >255 chars.

If I have the col type = DT_Ntext in my Data Flow, the package fails for files that do not have any values >255 chars.

If I have the external coltype=dt_wstr and the output coltype=dt_wstr(4000) the package fails if the file contains any value >255 chars.(Implicit conversion does not occur, as expected).

I worked around by adding a dummy first row with >255 chars.

Is there a way to use a cast function to solve this prob? I tried using Select dt_ntext(fieldname) from Sheet1$, but that does not work.

Is there some clean way to get around this problem?

TIA
Kar

Hi Karfast

For now this have to be done manually. I was able successfully execute getting advise from Bob Bojanic

Follw these instructions carefully:

1) Go to Mapping option at Destination, Point your mouse for each of row having your above problem and its corresponding destination (note the Datatypes types and Lengths)

2) Open Advanced Editor for the Excel Source, Under Amend the Types and Lengths accordingly for all the columns in destination to match your source Columns' type and length

Thanks

Subhash Subramanyam

|||

Metadata of Excel sheets is often hard to deal with. The dummy first row seems as a clever solution to me. It is not elegant but the entire JET provider is far from that.

Select dt_ntext(fieldname) is not going to work as dt_ntext can be recognized only by SSIS and not by the JET provider. The more adequate query would be select LongText(fieldname), but I have no idea if JET can deal with it (don't have a way to try it at the moment).

HTH.

|||

Thanks, Bob.

I tried:

Select LongText(Fieldname) , and also tried out Memo etc. All these give a Undefined Function error.

I cant even find any help on this. There is apparently something called a Jet SQL Reference, but I couldnt find it. Not even in Access 2000 Local Help.

So I guess the dirty workaround is the only way :-(

Kar

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