September 9, 2008 at 12:41 am
Hi,
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?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 9, 2008 at 5:08 am
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.
August 21, 2012 at 3:50 pm
Well
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.
August 22, 2012 at 1:35 am
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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply