File System or SQL Server

Question

File System or SQL Server

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 26, 2012 at 7:07 am

#146713

I developed a lot of DTS Packages unlike SSIS.
I read that it is faster and easier to troubleshoot if you use the file system and that typically storing in MSDB is usually done when going from one SQL Server to another.
I never deployed an SSIS Package to the File System.
Any thoughts, ideas or suggestions.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

I use File System packages and execute them using DTExec from a CmdExec SQL Agent Step Type, not an SSIS Step Type.

I use XML Package Configuration files (.dtsConfig) for all config data so I can do an xcopy-style deployment of all SSIS packages to new environments, and the dtsx files are guaranteed to require zero changes when moving from one environment to the next. The dtsConfig files are the only items that differ from one environment to the next.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 2

PS This allows me to leave the "SQL Server Integration Services" service disabled on all my servers. I need to install it to gain the binaries necessary to run my SSIS packages from the command line, but I do not require the service to be running, allowing that memory to be used for better things, like my SQL Server buffer pool 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

opc.three (3/26/2012)
I use File System packages and execute them using DTExec from a CmdExec SQL Agent Step Type, not an SSIS Step Type.
I use XML Package Configuration files (.dtsConfig) for all config data so I can do an xcopy-style deployment of all SSIS packages to new environments, and the dtsx files are guaranteed to require zero changes when moving from one environment to the next. The dtsConfig files are the only items that differ from one environment to the next.

I need to read up on config files.

I suppose that I need to alter my SSIS Packages to use the XML FIles for configurations?

If you know of any basic articles on this please share with me.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

opc.three (3/26/2012)
PS This allows me to leave the "SQL Server Integration Services" service disabled on all my servers. I need to install it to gain the binaries necessary to run my SSIS packages from the command line, but I do not require the service to be running, allowing that memory to be used for better things, like my SQL Server buffer pool 😉

That is cool, thanks. I got the SSIS Service to run. I had it set to use the Network Service Account. I changed to to Local System for now and it works.

Thanks for all of the tips.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 5

Welsh Corgi (3/26/2012)
opc.three (3/26/2012)
I use File System packages and execute them using DTExec from a CmdExec SQL Agent Step Type, not an SSIS Step Type.
I use XML Package Configuration files (.dtsConfig) for all config data so I can do an xcopy-style deployment of all SSIS packages to new environments, and the dtsx files are guaranteed to require zero changes when moving from one environment to the next. The dtsConfig files are the only items that differ from one environment to the next.
I need to read up on config files.
I suppose that I need to alter my SSIS Packages to use the XML FIles for configurations?
If you know of any basic articles on this please share with me.

BOL is always a good start: SSIS Package Configurations (2008 R2)

Here is a video that talks about setting up Package Configurations. SQL Server Table stuff starts at 2:14. XML Configuration files, my preference, starts at 3:33:

SQLShare: SSIS Configurations

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Yes, of course BOL.:blush:

Thanks for the link to the video.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I just noticed that you can't go into design view if you store the SSIS Package in MSDB?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

opc.three (3/26/2012)
I use File System packages and execute them using DTExec from a CmdExec SQL Agent Step Type, not an SSIS Step Type.
I use XML Package Configuration files (.dtsConfig) for all config data so I can do an xcopy-style deployment of all SSIS packages to new environments, and the dtsx files are guaranteed to require zero changes when moving from one environment to the next. The dtsConfig files are the only items that differ from one environment to the next.

If I execute the SSIS Packages using the DTExec as a CmdExec or Intergrated Services Step it does not load any transformation task.

It only populates tables in which I had to replace the Transformation to an OpenQuery inside a Stored Procedure.

Code: 0xC0040019 Source: Load cyp_Receivable_Activity Load cyp_Receivable_Activity (SSIS.Pipeline)

Description: Data Flow objects cannot be loaded. Check if Microsoft.SqlServer.PipelineXml.dll is properly registered.

End Error Error: 2012-03-29 07:42:15.19 Code: 0xC001001

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 9

Never seen that error before... relevant? http://stackoverflow.com/questions/7898169/ssis-error-failed-to-set-the-xml-persistence-events-property-for-the-data-flow

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

opc.three (3/29/2012)
Never seen that error before... relevant? http://stackoverflow.com/questions/7898169/ssis-error-failed-to-set-the-xml-persistence-events-property-for-the-data-flow

I was coming back to my desk with a cup of coffee and all of a sudden the light bulb went on.

It was a permissions issue with the SQL Server Agent Account.

What a weird error.

Thanks for your help.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

Yes, thank you very much.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/