Solved

Export CSV file into accdb access db file directly through SSIS

Posted on 2013-06-12
9
1,059 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now