Solved

Export CSV file into accdb access db file directly through SSIS

Posted on 2013-06-12
9
1,222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
ClickHouse in a General Analytical Workload

We have mentioned ClickHouse in some recent posts, where it showed excellent results.

In this article on Experts Exchange, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.

 

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

628 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