Running SSIS from remote with WCF -
i have web service calling ssis no problem on localhost. when deploy it, doesn't run , doesn't give error. should change allow request remote? beleive there someting prevents requesting... code.
public class blaservice : iblaservice { [webmethod] [webget(uritemplate = "runssispackage/{id}")] public string runssispackage(string id) { try { string pkglocation = configurationmanager.appsettings["dtsxpath"].tostring(); package pkg; application app; dtsexecresult pkgresults; variables vars; string databasename, tablename, minid, maxid, screateddatetime, filepathtemplate, foldername; int64 filerowamount, filecount; using (sqlconnection sqlconnection = new sqlconnection(configurationmanager.connectionstrings["connectionstring.sql server (sqlclient)"].connectionstring.tostring())) { sqlconnection.open(); sqlcommand sqlcommand = new sqlcommand("select * dbo.table id=" + convert.toint32(id), sqlconnection); sqlcommand.commandtimeout = 0; sqldatareader reader = sqlcommand.executereader(); if (reader.read()) { databasename = reader["databasename"].tostring(); tablename = reader["tablename"].tostring(); minid = reader["minimumid"].tostring(); maxid = reader["maximumid"].tostring(); filerowamount = int64.parse(reader["filerowamount"].tostring()); filecount = int64.parse(reader["filecount"].tostring()); screateddatetime = convert.todatetime(reader["createddatetime"]).tostring("yyyymmddhhmmss"); filepathtemplate = configurationmanager.appsettings["outputfilepath"].tostring(); foldername = "bla_" + screateddatetime; if (!directory.exists(string.format(filepathtemplate + "\\{0}", foldername))) { directory.createdirectory(string.format(filepathtemplate + "\\{0}", foldername)); } app = new application(); pkg = app.loadpackage(pkglocation, null); vars = pkg.variables; vars["dbname"].value = "bla_passwordpool"; vars["filecount"].value = filecount; vars["filename"].value = "bla_" + screateddatetime + "_1of1.txt"; vars["filenameprefix"].value = "bla_" + screateddatetime + "_"; vars["filerowamount"].value = filerowamount; vars["i"].value = 0; //vars["outputfolder"].value = @"c:\ssis\blasifretakip\"; vars["outputfolder"].value = string.format(filepathtemplate + "\\{0}", foldername); vars["selectsqlquery"].value = "select sifre " + tablename + " id>" + minid + " , id<=" + maxid + " order id"; vars["startrowindex"].value = minid; vars["tablename"].value = tablename; pkgresults = pkg.execute(null, vars, null, null, null); if (pkgresults == dtsexecresult.success) { passwordpackdatainfoentity pp = new passwordpackdatainfoentity(convert.toint32(id)); pp.status = 2; pp.save(); return "success"; } } else { return "empty"; } } return ""; } catch (dtsexception e) { return e.message.tostring(); } }
this permission issue between iis , sql server (ssis engine).
in iis, @ app pool wcf app (iis folder) using. if in default pool, create new pool , assign utility account (to make things easier & isolated). account needs permission read files (configured) ssis package folder , needs admin permissions on target database.
here discussion thread explains several pieces puzzle. little wordy, thorough: http://social.msdn.microsoft.com/forums/en-us/sqlintegrationservices/thread/ff441dc3-b43b-486b-8be1-00126cf53812/
Comments
Post a Comment