SSIS Advice

[Lez]

Hi Guys,

I have few questions that hopefully will give me the correct way forward
with these issues.

As part of a project I receive 3 CSV files, all need to be imported into my
database requiring the following process

1. CSV flat file data to be imported into temp tables
2. temp tables need to update the data in the production tables.
3. Insert query needs to run to append any new data from temp tables.
4. This process needs to run once every 24 hours

I have been struggling trying to find the correct/best method to handle
this. I have been trying to create a bulk insert for the temp tables, but
have not been able to get it to work correctly due to the file text
delimiters from the header and field values.

I then discovered the SSIS facility and have been able to do a 'manual
import of these CSV files, but have been unable save these as a package and
then re-run without going through the wizard each time.

My questions are therefore:

1. How can I run a saved SSIS package once it has been created (I have tried
saving as a file and on server)
2. Can I run the package as a task
3. Can I use SSIS to run update and insert queries
4. As a complete novice to this, can you recommend any good online
tutorials/videos to learn from.

The final use of the project is using the SQL database as a backend for an
MSAccess front end application.

My setup is Product: SQL2005 (SQL EXPRESS) on Windows server 2008

Hopefully I have provided sufficient information, but if you require any
further info please ask

Regards
Lez

[Todd C]

Lez:
I'm not completely up on my SQL Express functionality, but I think it does
NOT allow SSIS package execution. Look for a feature compare chart on SQL
Express. Failing the abiltiy to run SSIS from SQL Express, you may need to go
with a linked server setup or bulk copy. Check BOL (Books-on-Line) for those
topics.

Lastly, SQL Express does NOT come with SQL Agent, the component that allows
you to schedule jobs to run. However, if you are creative with Windows Task
Scheduler and the SQLCMD command line utility, you will have all the pieces
you need to set it up on a timer basis. Basically, the Windows Task executes
a DOS command, which uses SQLCMD to log into your SQL Express instance and
execute a command there, perhaps a Stored Procedure. That sp then has all the
logical steps in it to do the bulk copies, INSERTS and UPDATES as needed.

MSAccess is good for a front-end. You can connect it directly to a SQL
database and NOT need to go through an ODBC connection. It supports Windows
Authentication (if you are part of a domain). You will want to design it as
an "Access Data Project" (file extension *.adp). Then when ready to 'push' it
out to your users, compile it to a *.ade file. This compiles everything,
strips out the source code, and leaves the users with an (Access) executable
where they cannot change functionality or modify the design.

Best of luck.
=====
Todd C

Post by Lez
Hi Guys,
I have few questions that hopefully will give me the correct way forward
with these issues.
As part of a project I receive 3 CSV files, all need to be imported into my
database requiring the following process
1. CSV flat file data to be imported into temp tables
2. temp tables need to update the data in the production tables.
3. Insert query needs to run to append any new data from temp tables.
4. This process needs to run once every 24 hours
I have been struggling trying to find the correct/best method to handle
this. I have been trying to create a bulk insert for the temp tables, but
have not been able to get it to work correctly due to the file text
delimiters from the header and field values.
I then discovered the SSIS facility and have been able to do a 'manual
import of these CSV files, but have been unable save these as a package and
then re-run without going through the wizard each time.
1. How can I run a saved SSIS package once it has been created (I have tried
saving as a file and on server)
2. Can I run the package as a task
3. Can I use SSIS to run update and insert queries
4. As a complete novice to this, can you recommend any good online
tutorials/videos to learn from.
The final use of the project is using the SQL database as a backend for an
MSAccess front end application.
My setup is Product: SQL2005 (SQL EXPRESS) on Windows server 2008
Hopefully I have provided sufficient information, but if you require any
further info please ask
Regards
Lez

[Lez]

Hi Todd, many thanks for that.

I have seen your blog and will refer to it in future as I try to get my head
around SSIS.

Just to ask finally, using my local copy has SQL Server Agent which allows
me to set a master and target database. Can I ask is this designed to allow
control from a local to a server database to run task without the need to
have Server Agent on the server?

Many thanks
Lez

Post by Todd C
I'm not completely up on my SQL Express functionality, but I think it does
NOT allow SSIS package execution. Look for a feature compare chart on SQL
Express. Failing the abiltiy to run SSIS from SQL Express, you may need to go
with a linked server setup or bulk copy. Check BOL (Books-on-Line) for those
topics.
Lastly, SQL Express does NOT come with SQL Agent, the component that allows
you to schedule jobs to run. However, if you are creative with Windows Task
Scheduler and the SQLCMD command line utility, you will have all the pieces
you need to set it up on a timer basis. Basically, the Windows Task executes
a DOS command, which uses SQLCMD to log into your SQL Express instance and
execute a command there, perhaps a Stored Procedure. That sp then has all the
logical steps in it to do the bulk copies, INSERTS and UPDATES as needed.
MSAccess is good for a front-end. You can connect it directly to a SQL
database and NOT need to go through an ODBC connection. It supports Windows
Authentication (if you are part of a domain). You will want to design it as
an "Access Data Project" (file extension *.adp). Then when ready to 'push' it
out to your users, compile it to a *.ade file. This compiles everything,
strips out the source code, and leaves the users with an (Access) executable
where they cannot change functionality or modify the design.
Best of luck.
=====
Todd C

Post by Lez
Hi Guys,
I have few questions that hopefully will give me the correct way forward
with these issues.
As part of a project I receive 3 CSV files, all need to be imported into my
database requiring the following process
1. CSV flat file data to be imported into temp tables
2. temp tables need to update the data in the production tables.
3. Insert query needs to run to append any new data from temp tables.
4. This process needs to run once every 24 hours
I have been struggling trying to find the correct/best method to handle
this. I have been trying to create a bulk insert for the temp tables, but
have not been able to get it to work correctly due to the file text
delimiters from the header and field values.
I then discovered the SSIS facility and have been able to do a 'manual
import of these CSV files, but have been unable save these as a package and
then re-run without going through the wizard each time.
1. How can I run a saved SSIS package once it has been created (I have tried
saving as a file and on server)
2. Can I run the package as a task
3. Can I use SSIS to run update and insert queries
4. As a complete novice to this, can you recommend any good online
tutorials/videos to learn from.
The final use of the project is using the SQL database as a backend for an
MSAccess front end application.
My setup is Product: SQL2005 (SQL EXPRESS) on Windows server 2008
Hopefully I have provided sufficient information, but if you require any
further info please ask
Regards
Lez

[Todd C]

Lez:
Not sure on that one. I have never tried to use SQL Agent to do something on
another instance.
=====
Todd C

Post by Lez
Hi Todd, many thanks for that.
I have seen your blog and will refer to it in future as I try to get my head
around SSIS.
Just to ask finally, using my local copy has SQL Server Agent which allows
me to set a master and target database. Can I ask is this designed to allow
control from a local to a server database to run task without the need to
have Server Agent on the server?
Many thanks
Lez

Post by Todd C
I'm not completely up on my SQL Express functionality, but I think it does
NOT allow SSIS package execution. Look for a feature compare chart on SQL
Express. Failing the abiltiy to run SSIS from SQL Express, you may need to go
with a linked server setup or bulk copy. Check BOL (Books-on-Line) for those
topics.
Lastly, SQL Express does NOT come with SQL Agent, the component that allows
you to schedule jobs to run. However, if you are creative with Windows Task
Scheduler and the SQLCMD command line utility, you will have all the pieces
you need to set it up on a timer basis. Basically, the Windows Task executes
a DOS command, which uses SQLCMD to log into your SQL Express instance and
execute a command there, perhaps a Stored Procedure. That sp then has all the
logical steps in it to do the bulk copies, INSERTS and UPDATES as needed.
MSAccess is good for a front-end. You can connect it directly to a SQL
database and NOT need to go through an ODBC connection. It supports Windows
Authentication (if you are part of a domain). You will want to design it as
an "Access Data Project" (file extension *.adp). Then when ready to 'push' it
out to your users, compile it to a *.ade file. This compiles everything,
strips out the source code, and leaves the users with an (Access) executable
where they cannot change functionality or modify the design.
Best of luck.
=====
Todd C

Post by Lez
Hi Guys,
I have few questions that hopefully will give me the correct way forward
with these issues.
As part of a project I receive 3 CSV files, all need to be imported into my
database requiring the following process
1. CSV flat file data to be imported into temp tables
2. temp tables need to update the data in the production tables.
3. Insert query needs to run to append any new data from temp tables.
4. This process needs to run once every 24 hours
I have been struggling trying to find the correct/best method to handle
this. I have been trying to create a bulk insert for the temp tables, but
have not been able to get it to work correctly due to the file text
delimiters from the header and field values.
I then discovered the SSIS facility and have been able to do a 'manual
import of these CSV files, but have been unable save these as a package and
then re-run without going through the wizard each time.
1. How can I run a saved SSIS package once it has been created (I have tried
saving as a file and on server)
2. Can I run the package as a task
3. Can I use SSIS to run update and insert queries
4. As a complete novice to this, can you recommend any good online
tutorials/videos to learn from.
The final use of the project is using the SQL database as a backend for an
MSAccess front end application.
My setup is Product: SQL2005 (SQL EXPRESS) on Windows server 2008
Hopefully I have provided sufficient information, but if you require any
further info please ask
Regards
Lez