UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Ssis Read From Accdb Text Fields, Any Versions    
 
   
MadPiet
post Feb 23 2020, 10:33 PM
Post#1



Posts: 3,550
Joined: 27-February 09



Sorry if I already asked this, but …

I have SQL 2019 Developer and SSDT 2016 installed. I was trying to do something stupid simple in SSIS... import from an ACCDB file. If there are no text fields in my Access DB, it works fine. But if I have text fields, SSIS treats them as NVARCHAR() columns (double-byte). If I convert the file to .MDB and use Jet4.0, the data imports just fine. Do I have to do a cast to convert the NVARCHAR() columns to VARCHAR() before SSIS (or the SQL Engine) will let me import that? If I remember right, it works fine if I create a DSN, too. Just not if I use SSIS and ACCDB file type.

Has anybody figured out how to do this? (Yeah, I can push through linked tables, but …) Or is this just not something worth fighting about? Just seems really stupid.

(SQL Server has like 2 newer versions... I'm using 2019, but can't select it from the dropdown)
Go to the top of the page
 
ipisors12
post Mar 6 2020, 12:57 PM
Post#2



Posts: 19
Joined: 14-March 19



I would try, in order:

1) import the raw values into a SQL table with datatypes that accept them just how they are, then use a stored procedure to cast and convert as necessary. This is what I always do - I hate fighting with messy looking data transformations in ssis.
2) Fight with messy data transformations in ssis
Go to the top of the page
 
MadPiet
post Mar 6 2020, 01:11 PM
Post#3



Posts: 3,550
Joined: 27-February 09



Thanks, Isaac. The super irritating thing is that if I use the Jet4.0 driver, it works fine.
Go to the top of the page
 
ipisors12
post Mar 6 2020, 01:36 PM
Post#4



Posts: 19
Joined: 14-March 19



I can see how that would be frustrating. The later versions of Access changed the whole 'text' paradigms, so who knows. I'll bet if you changed the Access field type to Long Text (or is it Memo? sorry been a while since I used Access.) then hopefully ssis will see it as varchar(max) or at least let you import as such.

But nvarchar is not a bad data type! Unicode is the codepage of the future.

I admire you for doing the Access-to-SSIS thing. I'm assuming it's a one time thing and not to be automated. If so, i have had some luck also in creating a l inked ODBC table in Access, then paste-appending local records into them. 65k at a time ... Ok, now that I type it out, it doesn't sound very good but works in small situations.
This post has been edited by ipisors12: Mar 6 2020, 01:37 PM
Go to the top of the page
 
MadPiet
post Mar 6 2020, 07:06 PM
Post#5



Posts: 3,550
Joined: 27-February 09



I was kinda doing it to learn SSIS. Turns out, I could create an Access FE with linked tables to my SQL Server DB, and then just run a few append queries. I just wanted to figure out how to do it in SSIS. (Interesting side note is that if I saved the databases in the old mdb format, and just used Jet4.0, I could do it all in SSIS no problem.) For the most part, I only use Access if I want to do something really weird that I can't figure out how to do in SQL Server, so it's not that big a deal. Just kinda irritating.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    29th March 2020 - 10:25 AM