• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

SSIS package: Import and Export data through flat files

Hello,

I need to make packages on two different servers.

From one server, I need to export data on daily basis in to flat files and another package will import data from those flat files to database.

But I need to make package in systematic way as below:

1. Configuration manager (so that manage at run time at deployment). FYI, i can manage it through XML configuration.

2. Logging i.e. how many insert count and update count from a particular table at destination server when import from flat files will be occur.

3. If session break in mid either import/export then all transaction should be rollback. Also, need to give option to user in XML configuration to execute package on date range basis.

4. How can i manage packages like first master records should be inserted and after that transaction records will be inserted so that Foreign Key constraints should not be violated?

4. The package should be executed by SQL Server Agent on daily basis.

Kindly give answers as gradually aforesaid questions.

Best Regards,
MohitPandit
0
MohitPandit
Asked:
MohitPandit
  • 3
1 Solution
 
MohitPanditAuthor Commented:
0
 
MohitPanditAuthor Commented:
Can anyone assist on it?
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Hi,
You're actually asking for too much here.
You'll need to research each of your points and break it down .

1-read about ssis package configuration
http://www.sql-server-performance.com/2007/package-configuration-2005/

2-ssis custom logging
http://pragmaticworks.com/Products/Business-Intelligence/BIxPress/ssis-logging-auditing-monitoring.aspx

3-ssis checkpoints
http://www.mssqltips.com/sqlservertip/1408/integration-services-checkpoints-to-restart-package-from-failure/

4-you could use your own logic in sql query or lookup task in ssis
http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/2289.aspx

5-schedule ssis packages using SQL agent
http://decipherinfosys.wordpress.com/2008/09/17/scheduling-ssis-packages-with-sql-server-agent/

Hope that helps answer all your questions.

Regards,
Jason
0
 
MohitPanditAuthor Commented:
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now