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 Connection Manager expression editor - variable problem

Hi,

I have a data flow task within a foreach file loop. My problem occurs when I tried to make up an expression for the connection string of the Excel connection manager. Somehow I can see only system variables and none of the variables which I set up within the scope of the foreach file loop.

Can anyone provide me any insight into this mystery? As far as I can see, all the variables I created in the foreach file loop are still showing on the variable windows (without the Show All Variable button clicked).

Thank you very much and hope to hear from someone soon!

Regards,
Hsiao

The problem is the scope of your variables...move the variable at the package level so they are available to the connection manager.

Actually somewhere on this thread, there is an detailed explanation:

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

Rafael Salas

|||

Thank you Rafael. You're right about the variable scope. Somehow when clicking on connection managers, the variable list does not update itself. This seems to indicate to me that all connection-related variables need to be set up at the package level, including those that are used at a subordinate levels, such as containers and tasks.

Hsiao

Examples for using DirectRow method?

Hi!

I was just wondering if there are any examples on how to work with the Script Editor, and more specifically how to use the DirectRow method? I've seen some examples that say that the editor automatically creates the methods DirectRowTo<output buffer>, but it doesn't say how that is done. If I just specify Row.DirectRow(output buffer #), it says that the method is protected.

Any help or just pointing me in the right direction would be greatly appreciated!

Thanks!

Jeff Tolman
E&M Electric
jeff.tolman@.enm.com

Ok, I seemed to have found an example of using the DirectRow method in the ProcessInput method. I was initially trying to write my code in the ProcessInputRow method because I need to direct each row to 1 of 3 output buffers. Is this not possible? Here's a small summary of what I'm trying to do for each row:

If this row needs to be deleted then
Send it to the DeleteBuffer
Else
If the destination table already has the row then
Send it to the UpdateBuffer
Else
Send it to the InsertBuffer
End If
End If

Is a Script Transformation component the best way to do this? I'm a newbie when it somes to SSIS projects so I'll accept ANY suggestions at this point!

Thanks!

Jeff

|||

This should be a big help: http://www.sqlis.com/default.aspx?311

-Jamie

|||

That's what I needed!

Thanks Jamie!