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?
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?
try this
Exec Master..xp_cmdshell 'dtexec /f c:\UrPackagename.dtsx'
Exec Master..xp_cmdshell 'dtexec /f c:\UrPackagename.dtsx'
ASKER
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.
>>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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
ASKER
PS: I cannot use Microsoft.SqlServer.Manage