For anyone using the SQL Express 2005, and going over or got over the frustration of not having the DTS… and more over not able to use SSIS as some type of runtime that you distribute with your packaged software application…
Well, i thought i would share what i did, it is not rocket science, but works to extract the data without writing another module to maintain, rather using only command line or a batch file using sqlcmd.
If you haven’t explored sqlcmd, please do so, just go to the command line, and type sqlcmd/?, you can all kinds of options, and particularly we can look at the options that will let you execute the sql directly or better, you can pass a file with set of sql or stored procedure to execute.
I could have done this creating a stored procedure, but i didn’t want to modify the 3rd party application’s database, so i wrote a getxmldata.sql which outputs a results in xml (you can find everywhere how to output data in xml format using tsql)
then i wrote a batch file getxmldata.bat
sqlcmd -Slocalhost\MyDatabaseInstanceName -E -d MyDatabaseName -i getxmldata.sql -o output.xml
Something more to explore..
You can pass parameters to the your batch file and pass it to your sql file .. to make it more dynamic..
and if you want to schedule this bat file to run, frequently say every one hour or every 15 minutes.. you can use the windows scheduler to run this batch file..
you know where to find the windows tasks scheduler? it has a nice wizard to do what you want to do… if you can’t find it.. ask me…
