Solved

Export CSV file into accdb access db file directly through SSIS

Posted on 2013-06-12
9
1,080 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

920 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

16 Experts available now in Live!

Get 1:1 Help Now