SSIS - Package Configurations

One of the transitions from a beginner to an intermediate developer in SSIS is the ability to configure packages that you have created en mass. This can be done in a variety of ways but the best way is by use of Package configurations, this area of SSIS is very easy to setup but requires some discipline from your part, as the old adage goes "too many eggs in one basket", the same is true here. Basically this file will potentially control all of your data sources so with one adjustment to ONE file you can alter potentialy hundreds of packages, but at the same time one mistake can cause potentially hundresd of packages to fail. So tread carefully!

With that discussed, this video will show you the basics and the rest is up to you! Just like anything else in the BI workspace it's an artform NOT an exacting science. So please sit back and watch this video and do hope it helps all you budding SSIS people!

This is one of many videos which can be seen at PCTeach.me. Come join the gang!

Category:

Education

Tags:

• BI
• Business Intelligence
• Database
• Export
• package configuration
• SQL
• SSIS
• Training
• Example
• Microsoft
• Package
• Config
• Configurations
• Package Configurations
• training
• demo
• tutorial
• teach me
• howto
• how to
• tips & tricks
• SQL server
• Microsoft SQL
• Microsoft SQL Server
• technology
• tips and tricks
• tips
• instructions
• database
• data
• educational
• Software Tutorial

[asterix1986tps]

thnx a lot , that was very useful

[PCTeachME]

@asterix1986tps Thanks for the feedback!

[RakeshMCM]

Its really good and helpful, but i have question to ask,

1) Can we define file name in Deployment Configuration?, let say customer going to send data in different file names, so i want to configure like "C:\ToSSIS\*,csv", how to do that?

2) Also is it possible to Trigger SSIS Package automaticaly on receive of file in above folder?

Thanks

[PCTeachME]

@RakeshMCM Hi,

Very good questions! I will respond to the order which you asked:

1. This is not so much package configurations. I imagine what you are after is the ability to do multiple inserts/updates based of a series of excel files. If this is the case then you need to do a loop rather than package configs. A "For Each" loop (look on the toolbox) will allow you to select the same file types(same folder) and then loop through them.

cont...

[PCTeachME]

@RakeshMCM ...cont...

2. A package can either be called after the process is complete by use of the "execute package" task.

Alternativley, you could set up a package which the SQL Server agent would run at an interval of your choosing (e.g. run package every 5 mins). I would always reccomend the first approach as this gives you much more control over the logic where as the SQL Server agent schedule is running regardless of the outcome of your excel import.

Hope this answers your questions.

[iortizvictory]

Wow! Awesome video, this information is so hard to find on the Internet! Great info!

[PCTeachME]

@iortizvictory Thanks for the feedback. Make sure you subscribe to this channel or my website to be kept informed of new vids/articles. If there is anything you want to know about or was across to ask. Just give me a shout!

[iortizvictory]

Wow! This video is fantastic! Thanks for all of your help!

[PCTeachME]

@jimcasper12 Nice to be loved ;)

Okay! There are definate advantages to using a SQL configuration table over XML. If you follow the video up to the point I create the XML file, all you need to do is change the config type to SQL. It should ask you for a database connection from connection manager, either create a new one or choose an existing. Since this is the first time you are creating one click "New" on the configuration table and then follow the video as before. Job Done! Good Luck!

[jimcasper12]

WOW!. You just saved me 4 hour of reading on this subject. Thanks a lot.

One request could you also tell us how to configure the same using Sql server table. Keep up the good work, we all at ProWiz love you.