import from access

[Programmer99]

I have a linked table to a text file in an access database and am trying to import that linked table into SQL 2005 database. The problem I am having is when I go through the SSIS import designer and choose the acccess database source file, those linked tables do not show up nor do any queries in the access database. The only tables that show up are the ones that got created in access. Is there any way to point at access queries or linked tables to import them into SQL 2005? Thanks.

 

[Catadmin]

Sounds like the chain was broken.

If the tables in Access are linked to flat files, why not just create a Flat File Connection and link your SSIS package directly to the text files? You'd have to do a little formating in the FFC, but other than that, it should work the way you want it to.

And if you still need tables from the Access db that were created in the Access db, just create an Access connection in the same package and process those tables. Then you can use a Merge or Merge Join or Execute SQL task to get the data all in the same place.

Hope this helps.


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Programmer99]

I tried just connecting to the flat file and had some issues I could not get around. The main issue I had was that I could not get the formatting to recognize the carriage return lines to every line gets thrown off by one from the previous line. I changed all the options to CR/LF, LF, CR and about every other option but still did not work. I'm not sure what system creates the file since we get it from an outside business partner. I can see the line feeds so I know they are there. I also wrote a VB program and the program recognized them. I would prefer to use SSIS so if you have any suggestions, please let me know. Thanks.

 

[Catadmin]

I ran into some weird stuff with the Flat File Connection when I was doing a package last week. I had to go into the Connection -> Edit, make sure it was pointed to a sample file, then I either went to Advanced and clicked "Suggest Types" or stayed on General and played with the Format.

Play with the Delimited, FixedWidth and RaggedRight formats and see if that helps you out. Go through the Advanced, Column and Preview tabs after each one to see what it does to your sample file data. Then, after you find a format that works, try your line delimiter option again and format the column delimiters under Advanced.

Hope this helps.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"