Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSIS using SQl cmd???? Expand / Collapse
Posted Tuesday, September 9, 2008 12:41 AM



Group: General Forum Members
Last Login: Tuesday, July 19, 2016 4:34 AM
Points: 2,850, Visits: 4,076

For creating a SSIS package I am using “SQL Server Business Intelligence Development Studio” that provides me a facility to create SSIS through user interface. Is there any other method to create SSIS (or DTS) using SQL command or code? If yes, how?

I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #565928
Posted Tuesday, September 9, 2008 5:08 AM


Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, Visits: 23,078
Not SQLCMD. SSIS is not built using T-SQL. There is a set of .Net objects for the SSIS object model. You can programatically create packages using these objects. Search google for some articles. Also, an SSIS package is an XML file, I suppose you could directly generate the XML if you knew the structure well enough.
Post #566038
Posted Tuesday, August 21, 2012 3:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 26, 2016 10:31 AM
Points: 35, Visits: 122
even you knew you the structure of your pkg xml well enough, it will be a big deal to modify / create your package from xml directly as the XML is really big and bulky and complex (you can use it however to, lets say undo a change....Ctrl +Z....which is otherwise not applicable in SSIS BIDS user interface)

The ways to create your SSIS :

BIDS Designer (which you already know)

.NET code using SSIS Object Model
Of course you can call your .net exe / dll using sqlcmd....

Using some undocumented SQL Server Stored Procedures(but really should not be done this way)
Basically, SQL Server has some SPs(undocumented) like sp_OACreate, sp_OAExecute to create COM objects etc. So you can use such SPs to use the SSIS Object Model. But like i said above, that will be very very difficult and really ahoudl never be attampted.

Hope this helps.
Post #1348122
Posted Wednesday, August 22, 2012 1:35 AM



Group: General Forum Members
Last Login: Today @ 12:36 PM
Points: 7,950, Visits: 19,047
You may also like to read about BIML - a modelling language that can be 'built' into an SSIS package from within BIDS Helper (which is free). We use it here and can regenerate lots of packages in seconds (v useful in a warehousing environment if you have lots of packages doing essentially the same thing, just for different tables).

Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Just when you think your code is idiot-proof, the world invents a better idiot.
Post #1348216
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse