I built an SSIS package using Visual Studio but it takes about 20 hours to run. Meanwhile if I just use SQL Server Import/Export Wizard, it takes about an hour. So my question is - is it possible to run DTSWizard.exe from a command line?
My ultimate goal is to automate the data import process and run it monthly
SSIS is a tool for performing Extract, Transform and Load (ETL) operations.
The DTSWizard (Import/export wizard) generates an SSIS package. Visual Studio also generates an SSIS package. The biggest difference between the two is that the import/export wizard doesn't really allow you to perform transforms beyond data type conversions. A second difference is that the Import Export wizard can be used across all versions of SQL Server, including Express. If you only have Express Edition installed, then the Save Package feature will be disabled.
The approach the import/export wizard takes is that it adds source to destination up to N (5 I think) inside a data flow. If you've selected more than N source to destinations, then it adds more Data Flow Tasks. By virtue of this design "pattern", those first N transformations run in parallel aka at the same time.
If you are experiencing a 20 fold increase in time between the package you designed and the one emitted by the DTS wizard, then you likely have your data flow tasks running in serial. Remove the precedent constraints between them and they should run in parallel and net you the 1 hour run time.
To directly answer the question, they don't publish the parameters for the DTSWizard but you can observe that it does accept them because when launched from SSMS, depending on whether you selected Export Data or Import Data, it prefills the source/destination component as an OLE DB Connection Manager of SQL Server type.
In case you think you're going to be clever and rename the existing DTSWizard.exe to _DTSWizard.exe and then create a batch script with the same name that writes out the parameters passed to it to capture what SSMS does but that crashes your instance of SSMS so don't do that.
So, you can launch the DTSWizard.exe from the command line but you cannot specify the source and destination connection managers + all the tables/files involved. The performance difference you are experiencing is likely due to design and no intrinsic benefit to using the wizard over "regular" SSIS.