Solved

Export CSV file into accdb access db file directly through SSIS

Posted on 2013-06-12
9
1,176 Views
Last Modified: 2016-02-11
Hi,
I`m new in using SSIS, but i hear that i can use it to run a schedule to export csv file into access db file .
I`ll be glad if someone guide me steps to do that.
i have ssis 2008 vitual studio.
0
Comment
Question by:drtopserv
  • 3
  • 2
9 Comments
 

Author Comment

by:drtopserv
ID: 39243803
I have found intersting article explain somehow the steps to do to perform inporting csv file to access table through ssis. but still need more explination how to do it.
http://geekswithblogs.net/sathya/articles/how-to-import-and-export-csv-files-directly-in-ssis.aspx
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 39244369
You will be transfering data between two OLEDB connections, one to Access, another to the csv file. See this walkthrough as an example:

http://www.mssqltips.com/sqlservertip/2656/simple-step-by-step-process-to-import-ms-access-data-into-sql-server-using-ssis/

In your case instead of sql server you will have to configure OLEDB connection to the CSV file. Create separate folder and put CSV file in it. For the purposes of import, the folder will become "the database", and each file inside will be "a table". To describe the structure, create schema.ini file. See more information at http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353%28v=vs.85%29.aspx
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39244456
as an aside, you could use access with ODBC to a table in sql database, and have your SSIS append to the table....

What do you intend to do with the data once the "Csv" has been injected into an Access Table?
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:drtopserv
ID: 39245639
Well, my purpose is having the table in access, i have created a standalone application in access. and i need to users to views tables inside this access apps.
the point is that access at the main time have 1 linked csv files that the users use them through forms..
if the user run a form that it`s source is a linked csv . other users can`t open the same form.
(no multiuser can run same linked csv/excel files) in access.
that`s y i need to run a SSIS process to export this file.csv into access table everyday at night.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39253056
if you had an odbc linked table in access linked to the sql server table / view that you are exporting, your multiple users should all be able to open it.

If you use ODBC it will stop your access db bloating due to the data being re imported every night.
0
 

Author Closing Comment

by:drtopserv
ID: 39253164
Your linked article solve the problem.
thnx alot.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how the fundamental information of how to create a table.

679 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