January 22, 2015 at 1:14 pm
Hi,
I am new to SSIS and I need some help here, the problem is I have to go thru multiple MS Access database to import data from a table into SQL server. I have tried 2 options to try to get the job done but no success.
I tried try to use a Foreach file enumerator to browse thru a specific folder where all the access db are. I have set up a user::variable in the Variable Mapping to complete the setup, the complete file path is C:\Program Files (x86)\folder\folder\folder and the file extension is set to *.mdb.
That part seem to be OK.
I have an "connection manager" that points to a specific .mdb file in the directory and a second connection to my SQL server.
when I try to configure the OLEDB data source, I use the "connection manager" in the OLE DB connection manager and use the Table name or view name variable as the data access mode and the variable user::variable .
When I try to see a preview of the data I get the following: There was an error .......
Index and length must refer to a location within the string. Parameter name: length(mscorlib)
Beside that, when I want to close the editor, I get another about DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager MSAccessFile failed with error code 0x0202009....
How can I solve this problem, I am getting frustrated on this.
The other option I have tried is to read the file location path from the SQL server db using a foreach ADO enumerator, the container part is working fine but when I to set up the OLE db data flow source and try to preview somethingI get a different error and when I try to close the editor I get another DTS_E_CANNOT...... error
So, I would like to know what is causes these errors and how I can fix this
Thanks
Al
January 26, 2015 at 12:38 pm
A few questions.
The table in question in all these databases, is it the EXACT SAME schema? If not, you're going to have issues regardless.
Next, can you connect to a single one of these databases as a source without using expressions or variables? If so, step up, one step at a time, from that point, until you engage the error.
Is it the database name in a variable? Is it the path in a variable? Is it that it's Null before the ForEach Loop sets it and you need to have it bypass validation?
There's just too many moving parts. You need to know the exact point where it switches from working to non-working. This sounds like a driver failure to me, which is why I'm curious if you've been able to link into these databases at all.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply