Mapping from flatfile to SQL server database...

Hi!
Im trying to find existing software to map from flatfiles (mostly .txt) to a
database running on MS SQL Server. The files are recieved by mail, and made
available to my program, and are supposed to be read and processed in a way
that extracts the data from the files and inserts it into the database.
If I had the time and skill, I would love to do this myself, but under the
current circumstances i need to find some sort of existing SDK to do this (o
r
an easy way to program it).
Any suggestions?Christian Johansen wrote:

> Hi!
> Im trying to find existing software to map from flatfiles (mostly .txt) to
a
> database running on MS SQL Server. The files are recieved by mail, and mad
e
> available to my program, and are supposed to be read and processed in a wa
y
> that extracts the data from the files and inserts it into the database.
> If I had the time and skill, I would love to do this myself, but under the
> current circumstances i need to find some sort of existing SDK to do this
(or
> an easy way to program it).
> Any suggestions?
Have you considered using DTS or Integration Services?
http://www.sqldts.com/
www.microsoft.com/sql/technologies/...on/default.mspx
Alternatively, there are many integration tool vendors whose products
will do the job. Here is a selection:
www.abinitio.com
ibm.ascential.com
www.datamirror.com
www.datawatch.com
www.embarcadero.com
www.informatica.com
www.pervasive.com
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> Christian Johansen wrote:
>
> Have you considered using DTS or Integration Services?
> http://www.sqldts.com/
> www.microsoft.com/sql/technologies/...on/default.mspx
> Alternatively, there are many integration tool vendors whose products
> will do the job. Here is a selection:
> www.abinitio.com
> ibm.ascential.com
> www.datamirror.com
> www.datawatch.com
> www.embarcadero.com
> www.informatica.com
> www.pervasive.com
> --
> David Portas
> SQL Server MVP
> --
>
Ahh, just what I was looking for!
Thank you very much. Merry Christmas :)

Manual Instal of AdventureWorksToGo database in mobile client software factory (mcsf)

I want to manually install the database for the reference implementation (I have SQL 2005 Express when I installed the Factory, but AdventureWorksMobileStaging database is not installed in my sqlExpress, so i try to install manually in that database ) I have noticed a number of scripts under the AdventureWorksToGo\DBScripts folder. Is there any specific order of installing those scripts?Any other things that need to be followed for getting the reference database up and running?Thanks in advance

Varisai

Hi Varisai,

You should ask this type of question in the MCSF workspace, which you can find at http://www.codeplex.com/smartclient. The order of the scripts is specific to the implementation of this Patterns and Practice SDK and not really a part of SQL Express.

Mike

|||

Hi Mike,

Thank you, ya i go through codeplex.com and then i download Mobile Client Software Factory(MCSF) Hands on Lab and then i try to build that application, but it having a project called Mobile.PinAuthentication project, but i dont have any dll in MCSF like that. how i going to run it.

Thanks and Regards

Varisai.

|||

Hi Varisai,

Sorry I can't be much help here. MCSF is a sample app created by the group of people who own the CodePlex site, not by the SQL Server group, you need to seek help from them as I don't know anything about the implementation they use. There is a forum on the CodePlex site where you can post your questions and hopefully the owners will answer.

Mike

Manual Instal of AdventureWorksToGo database in mobile client software factory (mcsf)

I want to manually install the database for the reference implementation (I have SQL 2005 Express when I installed the Factory, but AdventureWorksMobileStaging database is not installed in my sqlExpress, so i try to install manually in that database ) I have noticed a number of scripts under the AdventureWorksToGo\DBScripts folder. Is there any specific order of installing those scripts?Any other things that need to be followed for getting the reference database up and running?Thanks in advance

Varisai

Hi Varisai,

You should ask this type of question in the MCSF workspace, which you can find at http://www.codeplex.com/smartclient. The order of the scripts is specific to the implementation of this Patterns and Practice SDK and not really a part of SQL Express.

Mike

|||

Hi Mike,

Thank you, ya i go through codeplex.com and then i download Mobile Client Software Factory(MCSF) Hands on Lab and then i try to build that application, but it having a project called Mobile.PinAuthentication project, but i dont have any dll in MCSF like that. how i going to run it.

Thanks and Regards

Varisai.

|||

Hi Varisai,

Sorry I can't be much help here. MCSF is a sample app created by the group of people who own the CodePlex site, not by the SQL Server group, you need to seek help from them as I don't know anything about the implementation they use. There is a forum on the CodePlex site where you can post your questions and hopefully the owners will answer.

Mike

Manual Instal of AdventureWorksToGo database in mobile client software factory (mcsf)

I want to manually install the database for the reference implementation (I have SQL 2005 Express when I installed the Factory, but AdventureWorksMobileStaging database is not installed in my sqlExpress, so i try to install manually in that database ) I have noticed a number of scripts under the AdventureWorksToGo\DBScripts folder. Is there any specific order of installing those scripts?Any other things that need to be followed for getting the reference database up and running?Thanks in advance

Varisai

Hi Varisai,

You should ask this type of question in the MCSF workspace, which you can find at http://www.codeplex.com/smartclient. The order of the scripts is specific to the implementation of this Patterns and Practice SDK and not really a part of SQL Express.

Mike

|||

Hi Mike,

Thank you, ya i go through codeplex.com and then i download Mobile Client Software Factory(MCSF) Hands on Lab and then i try to build that application, but it having a project called Mobile.PinAuthentication project, but i dont have any dll in MCSF like that. how i going to run it.

Thanks and Regards

Varisai.

|||

Hi Varisai,

Sorry I can't be much help here. MCSF is a sample app created by the group of people who own the CodePlex site, not by the SQL Server group, you need to seek help from them as I don't know anything about the implementation they use. There is a forum on the CodePlex site where you can post your questions and hopefully the owners will answer.

Mike

Managing Stored Procedures / Functions for releases.

WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.

What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.

A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.

We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...

What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.

We have 8 developers in our team.

If anyone could help or give advice on this, it would be awesome.

Thanks.

You need some form of source control system. If you have Visual Source Safe then put the DROP/CREATE scrips in it and before any developer changes an SP they make sure it isn't checked out. They check it out, do some work, then check the new update script in.

If you are all using different databases the developer should run the script that is in Source Safe against their database before making changes to ensure they are working on the latest version.

|||

Thanks, That is currently what we're switching to.

I wanted to know if there's anything better we should be doing :D

now we need to write a file to throw them all together.

Managing large database

I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.

I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.

Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.

For this type of mission-critical application, I want to get some queries cleared:

(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?

(2) What considerations to keep in mind in the inital stage of database design?

(3) How to keep the design scalable and configurable to meet future needs?yes you can have a mirror image of the database in another server by configuring log shipping or database mirroring both are high availability and disaster recovery solutions............you can refer the below articles for the same,
www.sql-articles.com
www.sql-articles.com/articles/dbmrr.htm and
www.sql-articles.com/articles/lship/lship.htm regarding the rest i am not sure........you can configure mirroring or log shipping depending on the criticality of the database........
|||Initially, I would like to start with the Express Edition. Does Express Edition provides these features? I also want to know whether SQL Server 2005 now supports Multi-Version Concurrency Control (MVCC) now?|||

For database mirroring you can have your monitor server as express edition but the partners need to enterprise or std edition.......for log shipping you need to have either enterprise,std or workgroup edition..........

|||

Those are fairly broad questions but in regards to the other issues, a backup strategy is determined by the business needs. You need to look at any SLAs that will be in place, issues of data loss, time to recover, frequency of inserts and updates, frequency of log backups, location of backups, size of the database and backups, if backups go to tape or network storage, etc. A general starting point for many databases is a daily full backup and then log backups depending on many of the already mentioned factors. Having both scheduled and manual backups - I'm not sure what you mean by this. Most backup routines are scheduled, automated. You can always do an ad hoc or manual backup in addition. And you generally do additional backups prior to activities such as applying service packs, implemented changes.

The initial considerations would be to just follow normal database development practices in terms of normalization, keys, selecting appropriate data types for columns, placement of log and data files, usage of tempdb, security considerations, etc. Chosing your indexes and accounting for index space would be important as well as considering potential data archiving strategies that may be needed. You'd want to consider other maintenance tasks as well such as regular integrity checks, reorgs or rebuilds of your indexes to address fragmentation. You would want to consider the server as a whole, not just the one database and any impacts the various database have on each other, system resources.

-Sue

|||

Sue has given you lots of good info to consider.

I'd also add that you probably want to go with at least Workgroup Edition for a business-critical database such as this.

There are limitations built into Express which you will run into sooner or later (such as the 4GB limit on DB size).

Don't design your overall solution for a small corner and then grow - design for your eventual big picture and fill in the pieces as you get to them.

Managing large database

I am developing an office automation software for a government department. To start with, I have decided to first automate the salary section of the department. Because of some issues like, TCO, easy support and maintenance, scalability etc., I have decided to use SQL Server 2005 database with VB 2005 front-end.

I have planned the layout for this first part and found that in only employee salary table, 40,000 records per month will be stored. That comes out to be around 480,000 records annually. This is for one table alone, excluding data in other tables.

Since each section of the department will be integrated into this later, this application will become the backbone the department. The employee service, leave, salary, allowance, deduction and other records shall be maintained.

For this type of mission-critical application, I want to get some queries cleared:

(1) What backup strategy should be followed? I want to have schedule and maual backups both. Is there any way to have a mirror image on another server?

(2) What considerations to keep in mind in the inital stage of database design?

(3) How to keep the design scalable and configurable to meet future needs?yes you can have a mirror image of the database in another server by configuring log shipping or database mirroring both are high availability and disaster recovery solutions............you can refer the below articles for the same,
www.sql-articles.com
www.sql-articles.com/articles/dbmrr.htm and
www.sql-articles.com/articles/lship/lship.htm regarding the rest i am not sure........you can configure mirroring or log shipping depending on the criticality of the database........
|||Initially, I would like to start with the Express Edition. Does Express Edition provides these features? I also want to know whether SQL Server 2005 now supports Multi-Version Concurrency Control (MVCC) now?|||

For database mirroring you can have your monitor server as express edition but the partners need to enterprise or std edition.......for log shipping you need to have either enterprise,std or workgroup edition..........

|||

Those are fairly broad questions but in regards to the other issues, a backup strategy is determined by the business needs. You need to look at any SLAs that will be in place, issues of data loss, time to recover, frequency of inserts and updates, frequency of log backups, location of backups, size of the database and backups, if backups go to tape or network storage, etc. A general starting point for many databases is a daily full backup and then log backups depending on many of the already mentioned factors. Having both scheduled and manual backups - I'm not sure what you mean by this. Most backup routines are scheduled, automated. You can always do an ad hoc or manual backup in addition. And you generally do additional backups prior to activities such as applying service packs, implemented changes.

The initial considerations would be to just follow normal database development practices in terms of normalization, keys, selecting appropriate data types for columns, placement of log and data files, usage of tempdb, security considerations, etc. Chosing your indexes and accounting for index space would be important as well as considering potential data archiving strategies that may be needed. You'd want to consider other maintenance tasks as well such as regular integrity checks, reorgs or rebuilds of your indexes to address fragmentation. You would want to consider the server as a whole, not just the one database and any impacts the various database have on each other, system resources.

-Sue

|||

Sue has given you lots of good info to consider.

I'd also add that you probably want to go with at least Workgroup Edition for a business-critical database such as this.

There are limitations built into Express which you will run into sooner or later (such as the 4GB limit on DB size).

Don't design your overall solution for a small corner and then grow - design for your eventual big picture and fill in the pieces as you get to them.