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.
LVL 1
Auerelio VasquezETL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Auerelio VasquezETL DeveloperAuthor Commented:
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
0
Auerelio VasquezETL DeveloperAuthor Commented:
I attached this doc, to see if my settings are the issue.
0
Auerelio VasquezETL DeveloperAuthor Commented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ValentinoVBI ConsultantCommented:
You need to configure the Excel Connection Manager to use that v_filename variable, otherwise it will keep using your hardcoded path/file and indeed always import the same file, as noticed.

Select the Connection Manager and have a look at the Properties window.  Locate Expressions and open the popup by using the ellipsis button next to it.  Now set an expression for the ExcelFilePath property, based on User::v_filename.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Auerelio VasquezETL DeveloperAuthor Commented:
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 ?
0
Auerelio VasquezETL DeveloperAuthor Commented:
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 ?
0
Auerelio VasquezETL DeveloperAuthor Commented:
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.
0
ValentinoVBI ConsultantCommented:
"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...
0
ValentinoVBI ConsultantCommented:
Are you using a Data Conversion transformation in that data flow?  What is it supposed to do?
0
Auerelio VasquezETL DeveloperAuthor Commented:
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.
0
Auerelio VasquezETL DeveloperAuthor Commented:
i am using a data conversion. i'll post the image
0
Auerelio VasquezETL DeveloperAuthor Commented:
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
0
ValentinoVBI ConsultantCommented:
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...
0
Auerelio VasquezETL DeveloperAuthor Commented:
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... .
0
ValentinoVBI ConsultantCommented:
What component is failing then? (recognized by red color)
0
Auerelio VasquezETL DeveloperAuthor Commented:
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 ?
0
Auerelio VasquezETL DeveloperAuthor Commented:
Ok, figured out the issue, i was bringing in as .csv files, and not calculating for the "" qualifier. thanks. everything else works now.
0
ValentinoVBI ConsultantCommented:
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...
0
Auerelio VasquezETL DeveloperAuthor Commented:
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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.