Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

sql

Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

Excel Destination: write over existing data

I have a daily package that extracts some data and writes it into an excel file. I want to write over the existing data, but the excel destination only appends the next free location in the worksheet. I tried using a SQL task to grab the file, set all the cells = NULL and then run the rest of the package, thinking it would see the null cells as empty and write in them, but somehow it knows where the previous data ended and keeps appending further down in the workbook.

Does anyone know of a workaround so I do not have to delete and re-create the file everytime?

TIA,
Sabrina

You could use the FileSystem task to delete the file prior to inserting into it. This will (I assume) create a new version of the file.

-Jamie

|||

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

|||

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

|||

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?|||

BrinaD1 wrote:

Thanks. I've tried deleting the file with a file system task, and that does fail because the destination no longer exists when it runs. The file system task does not have a create file option in it. I was thinking I would have to use a script task to do this?

Rafael, I did not see a link the example you mentioned.

Thanks,

Sabrina

Sorry about the omission, Here is the link:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

After deliting the file; just use the execute sql task to create it back as explained in the link...

|||

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

|||

Rafael Salas wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

Rafael Salas wrote:

You can delete the file, but I think the validation of the excel connection manager will fail because the file does not exists; but there is a work around; after deleting the file, you can issue a Create table...using execute sql task in control flow that point to the excel connection manager to create the file. You can see an example here....

Will setting "DelayValidation=True" work in this case?

It should do. Perhaps the original poster could try it. Its a property of the data-flow by the way.

-Jamie

If I recall correctly, the DelayValidation=true would woulddo just that; delay the validation; but it won't make the connection manager to create the file if it does not exists...but I am with Jamie...test it and see it.

Well, I tried DelayValidation=True and it behaved as expected - still failed. I was able to use the SQL task to create the excel file, thank you Rafael! It is just strange to me that SSIS does not have an option somewhere to overwrite vs append the data.

Thanks!

|||

Its not really anything SSIS can do something about - its a lack of functionality in the Excel Driver. Hopefully there'll be a better one coming one day.

-Jamie

|||

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

|||

BrinaD1 wrote:

Ok, same project, now with a twist.

I'm using the file system task to delete the file, and the Execute SQL task to re-create the file. The only problem is we want to add more worksheets. I've tried the following with no results:

CREATE TABLE `Perfect Order` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`PCTPERFECT` DOUBLE PRECISION
);
CREATE TABLE `Days to Activate` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);
CREATE TABLE `Days Activate to Ship` (
`GLYEAR` INTEGER,
`GLMONTH` INTEGER,
`DAYS` DOUBLE PRECISION,
`NUMLINES` INTEGER
);

Is there a way to do it all in one statement? Or do I need to have multiple Execute SQL tasks with the same Excel connection?

I would expected putting all the create statements in a single Execute Sql Task to work. Perhaps having multiple Execute sql tasks is the solution.

Excel Destination Number Conversion Error

In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?

Are you specifying Import Mode by using IMEX=1 in your connection string? This forces all data to text.

The Excel driver does store what it believes to be text data preceded by the apostrophe, to force the Excel application in turn to recognize it without question as text values. This implies to me that the columns in question contain at least some values that the driver is interpreting as text.

The BOL topic on the Excel Source contains some information that explains some of the behaviors of the Jet Excel driver.

-Doug

|||

Doug,

Thanks a lot for advice. I tried to put IMEX=0 (2) into a connection string, but it did not solve the problem. I wonder what the default is if IMEX parameter is not specified?

Sergey

|||

Sergey,

I was not encouraging the use of Import Mode...on the contrary, this forces everything to text and would presumably add the opening apostrophe everywhere.

You may want to try allowing the Import and Export Wizard to build your "base" package and add necessary Data Conversion tasks etc. for you. Then you can customize the package afterward.

Be careful to check that the driver is recognizing your numeric columns as numeric, by examining the data types of columns in the various components.

-Doug

Excel Destination Multiple Worksheets!

Hi ,

I am retrieving some data that contains three or four hundred thousand rows. These rows are supposed to go into an excel file with multiple worksheets, since one Excel worksheet cannot handle rows that are more than 65536. Below is what I need to achieve:

Dynamically create mutiple worksheets. Re-direct data like this i.e. first 64K in first worksheet and next 64K in next worksheet and so on. Dynamically name the work sheet with the start value in that work sheet e.g. OrderNumber or OrderDate.

Can we achieve this directly/indirectly?

Your help will be appreciated.

Thanks,

Paraclete

Search is your friend.

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

|||

Hi,

Thanks for your response. I already gone through this post. It does not explain how can we accomplish this. It just says it can be done in two steps. But the 2nd step which needs the elaboration it does not touch it.

Regards,

Paraclete

|||I'm just adding more information: Microsoft has a KB article on importing text files larger than 65,536 rows into Excel.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q120596|||

Hi All,

I tried to implement as described in the post below.

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

What happened first I wrote the results to a comma separated Flat file Destination. Then created a For Loop Container in which defined a Data Flow Task. It started with a Flat File Source to the access the data that was written to it earlier. Then added a Data Conversion Transformation. And finally the Excel Destination. There is a variable that is a result of an expression. Based on that expression I will create the name of the work sheet. When I try to do that, the following error apprears in the execution results tab

[Excel Destination [9]] Error: An OLE DB error has occurred. Error code: 0x80040E37.

[Excel Destination [9]] Error: Opening a rowset for "MySheet2" failed. Check that the object exists in the database.

This may be of interest to note that MySheet1 is already created before execution. And the variable value changed during execution i.e. 2nd iteration. I also tried to add a table using a Script Task but was not successful either. The question boils down to: Whether or not we can create a table whose name comes from a variable if yes then what is the syntax for that.

Below are the settings in Excel Destination Editor Connection Managers Tab

OLE DB Connection Manager = Excel Connection Manager (path and name of the excel file)

Data Access Mode = Table name or View name variable

Variable Name = DestSheetName (this is of string type constructed from expression)

Below is the table definition of "MySheet1". Can any one tell how to create this table using a variable name dynamically, since the value of 'DestSheetName' will change with every iteration of the Foor Loop Container.

CREATE TABLE `MySheet1` (
`EmpID_ID` INTEGER,
`LocID` INTEGER,
`Dept` NVARCHAR(6),
`Date_Time_Hired` NVARCHAR(20),
`RowGroup` INTEGER
)

Shema will remain same of every Table i.e. MySheet2, MySheet3 etc.

Thanks,

Paraclete

|||

You have to explicitly create each worksheet before trying to load it. The way of doing that is using a Execute sql task in the control flow with an create table statement that uses the excel connection manager. I know there are other threads in this forum that explains that. I have a post that shows it; it contains other logic, but hopefully you will get the idea:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

|||

Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement

Thanks,

Paraclete

|||

Did you check the link I provided? from that link:

Add an Execute SQL Task inside of the ForEach Loop Container and create a precedence constraint (yes, a green arrow!) from the Execute SQL Task to the Dataflow task.

Open the Execute SQL task and change the ConnectionType to ‘Excel’, Choose the Excel Connection Manager in Connection property and write a Create table statement in the SQLStatement as follows:

CREATE TABLE `Excel Destination` (
`HeaderID` INTEGER,
`HeaderName` NVARCHAR(50),
`LineID` INTEGER,
`LineName` NVARCHAR(50),
`LineDetails` NVARCHAR(50)
)

As you can see, the objective of this EST is to create the excel file to make it available to the data flow task on every iteration; and it is here where the power of the expression come handy.

Create a new variable, to hold the file name to be creates. I created a variable called 'ExcelFileName' of String Type (give an intial name in the variable window to avoid validation errors).

Now, use an expression to change the value of the variable at run time; to do so, go to the preperties windows of the Variable and change the porperty EvaluateAsExpression to true and click on the expression property to bring the expression editor. That expression will change the value of the variable as the package iterates through the recordset. I used an expression like:

"C:\\Temp\\Report-"+ (DT_STR, 25,1252)@.[User::HeaderID] +".xls"

In the Excel file connection manager, let's create another expression that uses the 'ExcelFileName' variable to change the ExcelFilePath. To do so, click in the connection manager, go to the properties window and click in the expression property. Once the Property Expression Editor is available; choose ExcelFilePath and bring the expression builder. Write an expression like:

@.[User::ExcelFileName]

|||

Paraclete wrote:

Yes, you are right we need to explicity create these work sheets before we can access them. Yes, we can try by putting this in a Execute SQL Task. But I doubt if the SQL Task can support CREATE TABLE Statement. Also what is the syntax for using a variable in CREATE TABLE Statement

Thanks,

Paraclete

Sure it can. You would create the statement as an expression-based variable and set the SQLSourceType of the Execute SQL Task to Variable.
|||

Same approach, different words:

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

Excel destination fails

Hi All!

I am trying to create a table destination to excel which fails with the following error that " Table cannot be created " System Error in Field definition" where as the same works in the Falt File Destination. What could be the problem?

Thanks

jas

It maybe due to the fact that excel has in built data types and column sizes attached to the columns whereas a flat file does not have that.

Try creating a table first and then map the excel columns to it to extract the records from the excel spreadsheet. When you import into a staging table, it is best to just keep the columns as varchar(255). If you have fields like remarks field that could be even bigger, you might want to customize your staging table.sql

Excel Destination errors for fields > 255 chars

Hi:

Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.

My oledb source columns(external and output) are both varchar (1000).

Works fine , but fails when values>255 chars.

Error 0xC0202009 on ProcessInput.

I use SQL2005 enterprise SP2.

I tried changing the Oledb source output to Textstream, but that does not work at all.

How do I export values >255 chars to excel?

TIA

Kar

Hello,

could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).

HTH.|||

Hi:

Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.

Kar

|||

Hmmm, I rejoiced too soon.

Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).

However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.

My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.

I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.

So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..

UGH.

How do I make sure the package works for all cases? Somebody please help me!

TIA
Kar

|||

Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.

Thanks.

Excel Destination errors for fields > 255 chars

Hi:

Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.

My oledb source columns(external and output) are both varchar (1000).

Works fine , but fails when values>255 chars.

Error 0xC0202009 on ProcessInput.

I use SQL2005 enterprise SP2.

I tried changing the Oledb source output to Textstream, but that does not work at all.

How do I export values >255 chars to excel?

TIA

Kar

Hello,

could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).

HTH.|||

Hi:

Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.

Kar

|||

Hmmm, I rejoiced too soon.

Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).

However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.

My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.

I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.

So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..

UGH.

How do I make sure the package works for all cases? Somebody please help me!

TIA
Kar

|||

Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.

Thanks.

Excel Destination appends the excel file everytime a package is executed

i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated

Perhaps you can use an Execute SQL statement with a delete statement before the data flow task. That way you delete existing rows before loading the new one.

Update: I just tried that and did not work:

[Execute SQL Task] Error: Executing the query "delete FROM [Sheet1$]" failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, another option may be to have an empty copy of the file that you copy/rename evry time the package run. You can use File system task in control flow for that.

|||

Pamela,

You could also use a Execute SQL task to create a new excel file on every execution of the package. I have posted an step by step guide on that:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

I hope you find it helpful

Excel Destination and Email Task Issue

Hi ,

I have a SSIS package in which at the end i have a excel destination task which imports a table data in excel files and then send it as an attachment using email task but i am facing a issue which is mentioned below -

1. The SSIS package runs as a SQL server agent job once in a day now every time the job runs i need to creat a new excel ( or delete the older one create again ) import data in that excel using Excel destination and then send it as an attachment using email task , How can i dynamically change the excel destination to point to a template file or new file

2. WIth the email task i am able to send only one attachment , i tried seperating the file names using semi colon but it is giving error "You dont have permission to access the file or file does not exists"

Please help....

Ashish

1. Create a template excel file and use it in file system task to copy into another file (new) which is used for attachment. After this, import the data into the excel file. In the send email task attach this file. Make sure the template file and the new file always exist in the required folder.

2. To send multiple attachments, you should use pipe characater | to delimit multiple files.

Ramani

Excel Destination

Does an empty file (text/ Excel) needs to be created manually for the connection manager for (text/excel)destinations. Can I create the file at run time?A file will be created for you.sql

Example code for consuming a SSIS datareader from ASP.net

Hi,
Does anyone have any or know of any demo code that shows how to consume data made available by a SSIS DataReader destination in an ASP.net app.

Thanks in advance.

-Jamie

P.S. Doug if you're reading this, it'd be handy to have some examples such as this in BOL or maybe even something provided with the samples.Re: How do I consume a DataReader Destination in VB.Net? - Microsoft Technical Forums
(http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=64798)

Doug mentions a topic, although I cannot check it right now.|||Cheers Darren.

The link to the article with the code in it is
http://msdn2.microsoft.com/en-us/library/ms215053

-Jamie|||I've struggled with the best description to provide of the issue where you must call DataReader.Read() one last time AFTER the final row of data, to avoid an exception.

Here is the current draft of the text, expanded from the version displayed in the topic that Jamie kind provided the link for:

"The Read method of this implementation of the DataReader returns true one more time after the last row of data has been read. This fact makes it difficult to use the usual code that loops through the DataReader while Read returns true. If your code attempts to close the DataReader or the connection after reading the expected number of rows, without an additional, final call to the Read method, the code will raise an unhandled exception. However if your code attempts to read data on this final iteration through a loop, when Read still returns true but the last row has been passed, the code will raise an unhandled ApplicationException with the message, "The SSIS IDataReader is past the end of the resultset." This behavior is different from that of other DataReader implementations. When using a loop to read through the rows in the DataReader while Read returns true, you need to write code to catch, test, and discard this anticipated ApplicationException on the last successful call to the Read method. Or, if you know in advance the number of rows expected, you can process the rows, then call the Read method one more time before closing the DataReader and the connection."|||Hmm... so much for the MSDN search integrated into BOL. I only have June BOL on hand, and did the online search pick that up, did it hell. Thanks.