Generating packages using C#

Hi,

we would like to generate SSIS packages(connections, data flow tasks, etc..) programatically using C#. Is it possible to do so?

At present we are creating packages by dragging and dropping required componets from toolbox and configuring them manually. We have a requirement of creating around 300 packages every week. Manual creation of packages is really a nightmare.

I heard from one of friends that using c# we can create packages. is it true? if so, can we view/edit the packages generated using c# using VS 2005 IDE(if required) ?

Regards,

Gopi

Hi,

there are several namespaces of Microsoft which can be used to generate you packages. But I am afraid that this is not as use-friendly as the GUI features of SSIS.

http://msdn2.microsoft.com/en-us/library/ms183036(SQL.90).aspx

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Try Books Online, Building and Saving Packages Programmatically, http://msdn2.microsoft.com/en-us/library/ms136076(SQL.90).aspx

Once saved, regardless of the method used to generate it, C#, VS/BIDS IDE, Wizard, you can still open it in any SSIS tool.

generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> That's SP2.
>> You can always find the service pack level by running
>> Select ServerProperty('ProductLevel')
> (But that doesn't tell you the version of client tools...)

generating database scripts - VERY SLOW

Has anybody else experienced how long it takes to generate scripts from
Management Studio? I'm launching the wizard (right click database/ tasks /
generate scripts). The wizard takes me through a couple of screen to set my
options. I finally get to the window where it's processing the objects.
After 10 minutes, only 3 objects have been completed. A database with
hundreds of tables and views will take hours.
Is there any way to speed this up? Right clicking on a table or view and
generating the DDL for that one object only takes a few seconds.
It was never this slow in SQL Server 2000.
The original release of SSMS was, yes, very slow. I think it is a bit
better in more recent builds, have you applied SP2 to your client tools?
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:7923D6FA-827E-4C50-85EE-64CCCEE21270@.microsoft.com...
> Has anybody else experienced how long it takes to generate scripts from
> Management Studio? I'm launching the wizard (right click database/ tasks
> /
> generate scripts). The wizard takes me through a couple of screen to set
> my
> options. I finally get to the window where it's processing the objects.
> After 10 minutes, only 3 objects have been completed. A database with
> hundreds of tables and views will take hours.
> Is there any way to speed this up? Right clicking on a table or view and
> generating the DDL for that one object only takes a few seconds.
> It was never this slow in SQL Server 2000.
|||Not sure what version I'm at, don't know if this is SP2 or not?
Microsoft SQL Server Management Studio 9.00.3042.00
|||That's SP2.
You can always find the service pack level by running
Select ServerProperty('ProductLevel')
Tom
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||> That's SP2.
> You can always find the service pack level by running
> Select ServerProperty('ProductLevel')
(But that doesn't tell you the version of client tools...)
|||Several connect items I have reviewed seem to indicate that Microsoft thinks
they have solved this problem (or in a couple of cases, they mention some of
the issues brought up in the item, but not the speed). It is unclear after
reading them all whether they think they have fixed this in SP2 or the
typically vague "future release(s)"...
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125313
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=228719
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126912
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=259838
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=262072
There are probably others; I did not perform an exhaustive search. Just
wanted to show you that you are not alone, and that some in Redmond seem to
think that it is fixed. :-)
"johnl" <johnl@.discussions.microsoft.com> wrote in message
news:C73B5E5D-5442-4619-BC54-75E697C9D733@.microsoft.com...
> Not sure what version I'm at, don't know if this is SP2 or not?
> Microsoft SQL Server Management Studio 9.00.3042.00
>
|||True. Thanks for catching that.
"Aaron Bertrand [SQL Server]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eJK5jXBnIHA.1212@.TK2MSFTNGP05.phx.gbl...
> (But that doesn't tell you the version of client tools...)

Generate table layout script

Hi,
Anyone knows that if I need generate a table layout without the EM right
click table All Tasks Generate SQL Script. I knew that bcp only export/input
the data but not generate script of the table without data.
Regards!
Chen
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:F75EF23A-F12D-46F4-AFA2-993335C92C05@.microsoft.com...
> Hi,
> Anyone knows that if I need generate a table layout without the EM right
> click table All Tasks Generate SQL Script. I knew that bcp only export/input
> the data but not generate script of the table without data.
> Regards!
> Chen

Generate table layout script

Hi,
Anyone knows that if I need generate a table layout without the EM right
click table All Tasks Generate SQL Script. I knew that bcp only export/input
the data but not generate script of the table without data.
Regards!
Chenhttp://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:F75EF23A-F12D-46F4-AFA2-993335C92C05@.microsoft.com...
> Hi,
> Anyone knows that if I need generate a table layout without the EM right
> click table All Tasks Generate SQL Script. I knew that bcp only export/input
> the data but not generate script of the table without data.
> Regards!
> Chen

Generate table layout script

Hi,
Anyone knows that if I need generate a table layout without the EM right
click table All Tasks Generate SQL Script. I knew that bcp only export/input
the data but not generate script of the table without data.
Regards!
Chenhttp://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chen" <Chen@.discussions.microsoft.com> wrote in message
news:F75EF23A-F12D-46F4-AFA2-993335C92C05@.microsoft.com...
> Hi,
> Anyone knows that if I need generate a table layout without the EM right
> click table All Tasks Generate SQL Script. I knew that bcp only export/inp
ut
> the data but not generate script of the table without data.
> Regards!
> Chen

Generate Exchange Task from T-SQL

Our organization would like to add tasks to users' Exchange accounts from our SQL Server using a USP. Basically, we are looking for the same functionality as the xp_sendmail syntax provides, but instead of sending an email to a user, we would like to create a task in the user's Exchange Tasks folder based on the information passed from our database via the USP.

Here is an example:

A client must receive paperwork every 6 months based on a date stored in our SQL database.

2 weeks prior to the date the paperwork is due, a USP would detect that John Doe has upcoming paperwork needed.

Bob is John Doe's sales rep. The USP would create a new task in Bob's Exchange Tasks folder indicating that John Doe's paperwork is due on such-and-such a date, setting reminders, etc.

We are currently running SQL 2000 and Exchange 2003 in an Active Directory environment. Any help or pointers would be greatly appreciated!!

Thank you - Jeremy

Best is to use CDO (Colloborative Data Objects) or Outlook Object Data Model outside of the database. You could write code using sp_OA* SPs but it is not going to be a robust implementation. You can use a SQLAgent job with ActiveXScript task to do the task creation. See below links for more details on how to use CDO and Outlook object model.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/exchanchor/htms/msexchsvr_cdo_top.asp

http://msdn2.microsoft.com/en-us/library/ms268893.aspx

There are lots of KB articles that contains code for using CDO / Outlook Object Model to create messages, appointments, items, tasks etc.