MS SQL

MS SQL RSS

SSIS Permission Problem

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Mar 2007

Posts: 155

Reputation: vuyiswamb is an unknown quantity at this point

Solved Threads: 5

vuyiswamb

Offline

Junior Poster

SSIS Permission Problem

33 Days Ago

Good Day All

I have a Package created and hosted in a Machine(Theresa) that has SQL 2008 and i have my Development Machine(Vuyiswa) that has IIS and am Debugging from the my machine. i have a ASP.NET 2.0 App and am executing a Package that in another machine. The First thing i did was to share a directory where the Packages are stored when i install the packages and supplied "Everyone","ASP.NET","Vuyiswa" Accounts permissions to to access this share. i can go into the share from the another machine without any problem. i have a code like this in my ASP.NET App

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 try
        {
            //Start the SSIS Here
            Application app = new Application();
            Package package = NULL;
            package = app.LoadPackage(@"D:\Program Files\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", NULL);
            //@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", NULL);
 
            VARIABLES vars = package.VARIABLES;
 
            vars["Time1"].Value = time;
 
            vars["Time2"].Value = time;
 
            vars["Time3"].Value = time;
 
            vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();
 
            //package.Connections["SQLNCLI10.1"].ConnectionString = obj.GetConnectionString(THREAD_DATA[0].ToString());
            //Excute Package
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
 
            IF (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError IN package.Errors)
                {
 
                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                    Console.WriteLine();
                }
            }
 
        }
        catch (DtsException ex)
        {
            Exception = ex.Message;
        }
 try
        {
            //Start the SSIS Here
            Application app = new Application();
            Package package = null;
            package = app.LoadPackage(@"D:\Program Files\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);
            //@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\OMEGA\OMEGA.dtsx", null);
           
            Variables vars = package.Variables;

            vars["Time1"].Value = time;

            vars["Time2"].Value = time;

            vars["Time3"].Value = time;

            vars["TTBLTYPE"].Value = THREAD_DATA[1].ToString();

            //package.Connections["SQLNCLI10.1"].ConnectionString = obj.GetConnectionString(THREAD_DATA[0].ToString());
            //Excute Package
            Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
          
            if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
            {
                foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                {
                   
                    Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                    Console.WriteLine();
                }
            }
           
        }
        catch (DtsException ex)
        {
            Exception = ex.Message;
        }

This execute Fine until the

Help with Code Tags

MS SQL Syntax (Toggle Plain Text)

 Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

it does not stop here , but i trap it here

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 IF (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
 if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)

and the Error am Getting is

"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D.\r\nAn OLE DB record is available. Source:\"Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'sa'

On my Package i use the "sa" credentials and my Protection Level is "Dont Save Sensative"

Thank you

Vuyiswa Maseko

Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."