Execute SSIS Package using T/SQL

Setup:
SQL Server 2005, VB.Net 2005 Windows Application

Problem:
at present i have a VB.Net program that creates CSV files, its opening the datareader and writing to the CSV File. the problem is when data size increases( in test environment data is about 220000 records going into 3 files, each file about 80K records) its taking time ( in test environment its varying between 30-45 minutes). so we searched for an alternative solution and found SSIS packages is faster than existing program. we created a test package and in the same environment its creating a file with 80K records in about 20-30 seconds(its really seconds). the only difference is the package is executed on the server itself. Now the problem is we have to execute the package from our vb application.
I know about commond line utility dtexec . To execute it from a stored procedure i have to use xp_cmdshell extended stored procedure. the main problem is in SQL server 2005 by default xp_cmdshell is disabled. and as per the enterprise security policy i cannot change it to enabled.

Now i am looking for other alternatives how can i execute a SSIS package using T/SQL.

Any solutions?

LVL 39
appariAsked:
Who is Participating?
 
derobyCommented:
I'm afraid you're kind of stuck then.

What we did to get around the "xp_cmdshell can't be used" limitation was create our own extended procedure that could only do one thing, namely launch the dtsrun.exe (=sql2k equivalent of dtexec.exe I presume), have it approved & insalled by IT and use that one for launching DTS (SSIS) packages.

IT indeed felt very uncomfortable about xp_cmdshell but was able to live with this solution.

PS: what I'm a bit curious about is : how can a SSIS package be a 100 times faster than what you wrote using VB.Net ???? Anything special that goes on ? I'm guessing here, but I'm pretty sure you're using a lot of lookups and (re)connects, I don't have a clue how else you could get this kind of 'performance'. My advice would be to put as much functionality as possible in a stored procedure(= read : put ALL functionality in a sp!), have that stored procedure return a recordset that can be directly written to the CSV file. All your VB app has to do is : Open a connection, execute a command, if recordset is returned than open file, walk through recordset and write to file, close file, close recordset, close connection. Et voila. Shouldn't take much longer than the SSIS package.

My 2 cents
0
 
appariAuthor Commented:


PS: I cannot use Microsoft.SqlServer.ManagedDTS in VB.net also, i need a solution purely using T/SQL.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
try this

Exec Master..xp_cmdshell 'dtexec /f c:\UrPackagename.dtsx'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
appariAuthor Commented:
please can you read the question completly, i cannot use xp_cmdshell as it is disabled and i cannot enable it becoz of security policy restrictions.

>>I know about commond line utility dtexec . To execute it from a stored procedure i have to use xp_cmdshell extended >>stored procedure. the main problem is in SQL server 2005 by default xp_cmdshell is disabled. and as per the >>enterprise security policy i cannot change it to enabled.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:

appari,
> To execute it from a stored procedure i have to use xp_cmdshell extended
> stored procedure.

Sorry,  I didn't see this part.. Refer the following links, these are the different ways to execute SSIS packages. I think probably the last one will work for you, it uses the sQl server agent

How to: Run a Package Using the DTExec Utility
http://msdn2.microsoft.com/en-us/library/ms138023.aspx 


Command Prompt Utilities (SSIS)  
http://msdn2.microsoft.com/en-us/library/ms140258.aspx 


Running Packages  
http://msdn2.microsoft.com/en-us/library/ms141708.aspx 


Alternatively, SQL Agent has a new feature called subsystems that makes it possible to directly execute packages.


SQL Server Agent Subsystems
http://msdn2.microsoft.com/en-us/library/ms187100(en-US,SQL.90).aspx 


0
 
appariAuthor Commented:

No, i cannot change any of the default settings or start new services. SQL Agent is also disabled by default. i need solution purely in t/sql
0
 
appariAuthor Commented:
deroby:
thanks for your response.

>>how can a SSIS package be a 100 times faster than what you wrote using VB.Net ????
even i am not sure what is causing that much of time if i am doing it using vb application. the dataset created is a simple sql. all the data creation part was done in a sp and insert final data in a temp table( its a permanant temp table, purpose is just to get the required data). after creating the data in temp table we just issue a simple "select fieldlist from  table name" and use the returned dataset in a loop to write the csv file.

for testing i created SSIS package and within the package used same sql and create the flat file. amazingly it created the file in just 18 seconds.

>>What we did to get around the "xp_cmdshell can't be used" limitation was create our own extended procedure that could only do one thing, namely launch the >>dtsrun.exe (=sql2k equivalent of dtexec.exe I presume), have it approved & insalled by IT and use that one for launching DTS (SSIS) packages.

can you give more details or sample of own extended procedure that could only do one thing, namely launch the dtsrun.exe.  if we can create the sp without changing any default settings on the server i can give it a try.
0
 
appariAuthor Commented:

i found this article Running a DTS Package Through TSQL with a COM Concept http://www.devx.com/tips/Tip/17053
i have to check if i can use this method, cant wait till Monday to give it a try.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.