SolvedPrivate

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

Posted on 2014-09-08
19
36 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:basile
  • 13
  • 6
19 Comments
 
LVL 1

Author Comment

by:basile
ID: 40310232
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
 
LVL 1

Author Comment

by:basile
ID: 40310250
I attached this doc, to see if my settings are the issue.
0
 
LVL 1

Author Comment

by:basile
ID: 40310253
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 40311455
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
 
LVL 1

Author Comment

by:basile
ID: 40311828
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
 
LVL 1

Author Comment

by:basile
ID: 40311836
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
 
LVL 1

Author Comment

by:basile
ID: 40311875
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40311909
"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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40311914
Are you using a Data Conversion transformation in that data flow?  What is it supposed to do?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:basile
ID: 40311915
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
 
LVL 1

Author Comment

by:basile
ID: 40311916
i am using a data conversion. i'll post the image
0
 
LVL 1

Author Comment

by:basile
ID: 40311922
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40311944
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
 
LVL 1

Author Comment

by:basile
ID: 40311991
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40312074
What component is failing then? (recognized by red color)
0
 
LVL 1

Author Comment

by:basile
ID: 40312088
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
 
LVL 1

Author Comment

by:basile
ID: 40312155
Ok, figured out the issue, i was bringing in as .csv files, and not calculating for the "" qualifier. thanks. everything else works now.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 40312174
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
 
LVL 1

Author Comment

by:basile
ID: 40314421
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now