• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10083
  • Last Modified:

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?

0
appari
Asked:
appari
  • 5
  • 2
2 Solutions
 
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
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
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:
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now