asked on

SSIS For Each Loop for Loopoing thru excel files, and importing

I am having an issue, and I don't know why, because i've done this a million times before. Trying to do a simple loop, for each file, and import files from a folder, into the db. i have a variable for the filename named v_filename, i set delay validation to true on the data flow task. Do i need to put a value for the variable, for the initial file to import? it seems that when i set the connection to a specific file in the folder, it just keeps importing the same file. I dont' need to move the files do i ? i don't ever remember this being the case. Very frusterating.

Auerelio Vasquez

ASKER

What the problem i think i'm having, it keeps only importing the same file, it's the same number of records being moved, it seems with every iteration..... i'll post some pics, of my settings

Auerelio Vasquez

ASKER

I attached this doc, to see if my settings are the issue.

Auerelio Vasquez

ASKER

C--Users-039824-Desktop-ssis-excel-issue

ASKER CERTIFIED SOLUTION

ValentinoV

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Auerelio Vasquez

ASKER

ooooh, i thought the expression was supposed to be set for the connection manager.... I'm gonna try that now. Should i populate that variable initially ?

Auerelio Vasquez

ASKER

that seems to be working a little better, but i'm getting this error from the excel spreadsheet,

[Excel Source [932]] Error: Opening a rowset for "'report-14-09-01-530am$'" failed. Check that the object exists in the database.

Is that because the worksheet names have to be exact ?

Auerelio Vasquez

ASKER

Changed the files to use flat file, and converted the excel files to .csv. now getting this error on dates:

[Data Conversion [1497]] Error: Data conversion failed while converting column "UTC_ Date Time" (1125) to column "_UTC_ Date Time" (1538). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion [1497]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "_UTC_ Date Time" (1538)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "_UTC_ Date Time" (1538)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

ValentinoV

"Is that because the worksheet names have to be exact ?"

Correct. It is possible to use a package variable to store the sheet name but in that case you'll have to write an expression that comes up with valid sheet names...

ValentinoV

Are you using a Data Conversion transformation in that data flow? What is it supposed to do?

Auerelio Vasquez

ASKER

yea, that is why i'm moving to .csv, but i'm getting some weird errors now;

such as above and this one Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

and this is for float columns, coming in from the .csv files. i tried to do a data conversion, but it's still failing.

Auerelio Vasquez

ASKER

i am using a data conversion. i'll post the image

Auerelio Vasquez

ASKER

ok, there is the table, and data conversion. i'm having issues with UTC_Date which is just a number, coming in to float, don't know why it would fail..
C--Users-039824-Pictures-table.jpg
C--Users-039824-Pictures-data-conversion

ValentinoV

Is the Data Conversion component red when the package finishes with the error? If yes, try debugging by redirecting the error flow to another component (such as Union All), this allows you to put a Data Viewer on the red arrow to see what data is causing the failure...

Auerelio Vasquez

ASKER

the data conversion has doesn't turn red, i keep getting this error [Data Conversion [1497]] Error: Data conversion failed while converting column "Year" (1137) to column "_Year" (1554). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

i've made all the columns in the table now numeric(18,0) instead of float. and i'm still getting these errors... .

ValentinoV

What component is failing then? (recognized by red color)

Auerelio Vasquez

ASKER

well, now i do see it failing on the conversion, it looks like it fails on all of the numeric conversions.

i keep getting these errors:

[OLE DB Destination [817]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

very frusterating. if i ignore all the columns that are numeric, it comes in just fine, and the loop works, now i have to figure out why the data is not converting. again, i've changed all columns to numeric that were float in the table (destination) im tempted to change to varchar, and see if that helps, but i should be able to convert incoming no? with the data transformation ?

Auerelio Vasquez

ASKER

Ok, figured out the issue, i was bringing in as .csv files, and not calculating for the "" qualifier. thanks. everything else works now.

ValentinoV

Well, that would indeed cause conversion issues, glad to hear you got it solved. As for conversion: I never use the Data Conversion transform, I prefer using the Derived Column for that. It's more flexible and supports copy/paste of the expressions...

Auerelio Vasquez

ASKER

Yes, i had thought to use the derrived column as well. I couldn't figure where it was letting me convert to unicode, but i got it figured out. It's been a while!