Hi, I have created a test package to load data into 2 tables. so created two simple insert scripts and created a batch file and calling batch file in execute process task. but i am getting error : Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:\sql.bat" "" at "", The process exit code was "1" while the expected was "0". and if i check in dos prompt, i am getting error Login Failed. Can somebody tell me how to call a batch file in ssis? the script i have used in batch file is SQLCMD -E -dHRMS_SITE -iC:\aTest1.sql SQLCMD -E -dHRMS_SITE -iC:\aTest2.sql

Well to load data to tables in SSIS we do not typically use a bat file. There are better ways - a Flat File Source to SQL Server destination and you are done. Looking at your troubles, SQLCMD -E -dHRMS_SITE -iC:\aTest1.sql used integrated security. Thus the account running the package cannot log into the local server (no -S switch specified)Arthur My Blog

Hello Arthur, I have bunch of sql scripts which needs to run after data load (OLEDB source to OLEDB destination). So thats the reason i need to run those scripts using .bat file but it would be great if you know any other way if i could run those scripts.

Hello Nick, sure, if you need to then please run by all means. The other way is using SQL Agent, but you may need the execute command shell permissions. I think you are not too far from making the existing .SQL file runnable, so not to fret. You are probably only missing the proper permissions. So in general it is this syntax with sqlcmd sqlcmd -SMyServer\InstanceName -E -iC:\MySQL.sql so make sure you specify the -S switch and that the account executing the package can indeed login under Wirndows Security. I think you need to check if you can login with your SSMS, I doubt you can. Arthur My Blog

Hello Arthur, tried it but getting Login Timeout expired error in dos. Wondering what should be the InstanceName. I have sqlcmd -S(Local)\InstanceName -E -iC:\aTest1.sql in .bat file.

You need to get the instance name Nick it is sometimes a named one e.g. abc/xyz and soemtimes default (same as the server name) so it is then -SABC You get the timeout because it was not located because in turn the name does not exist. Arthur My Blog

Hello Arthur, I fixed it. First thing i added USE [DatabaseName] in both the scripts. and just used the server name in .bat file code. sqlcmd -Swdwzp4065 -E -iC:\aTest1.sql It doesnt work on Local server though but thats fine. Thanks for your help.

Glad to hear, besides, you needed to add the database switch because the default data base was not the target one. And there is no reason why it would not work against local, it is just a matter of figuring the correct name and using it Nick.Arthur My Blog

Got it, didnt created the tables on Local server. Working now. Appreciate your help Arthur.