Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 60
  • Last Modified:

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.
0
Auerelio Vasquez
Asked:
Auerelio Vasquez
  • 13
  • 6
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now