Timeout exception in SSIS when using table variables

I had faced an issue with SSIS in which i needed to use table variables( as i dont have permission to create CTE). I wrote the query using four table variables and i got the proper output in management studio. This query was running for 54 seconds to retrieve the necessary data. But, when i implement this in SSIS OLE DB source, the data from table variables is not getting inserted in the destination table and i dint get any error as well when ran the SSIS package as a whole. When executed the package, it executed successfully. But in the data flow path it was not showing the number of rows returned from source. When checked in the destination table , It was empty.

When i debugging i found that this table variables were getting timeout (found when i used preview option) and its not taking more than 10 seconds to throw the error. I was wondering because, in management studio it took 54 secs and in SSIS it throws error in 10 seconds. I tried setting up the timeout property and changed the query to fix the issue and nothing worked. 

Finally found one article in MSDN in which its mentioned that table variables will return record count if the SET RECORDCOUNT option is set to off. So, i put that option is on of top of the page. Interestingly, still i was not able to ran package and getting timeout error. Then i put SET RecordCOUNT OFF just before all the table variables( i used four table variables). This solved my problem.  

Shop Amazon - Save on Temptations and Dentastix Products

System.Data.OleDb.OleDbException: Unspecified error

I was trying to access the excel sheet using  Oledb, but as soon as try to open the connection to that excel file i got error like "UnSpecified error"... tried with different drivers and nothing worked. Tried giving full access to 'Everyone' for the particular folder but nothing solved the issue.



the sample code is....

  if (_fullFilePath.EndsWith(".csv", StringComparison.CurrentCultureIgnoreCase))

                {

                    var dirPath = Path.GetDirectoryName(FullFilePath);

                    var fileName = Path.GetFileName(FullFilePath);

                    CreateCsvSchemaIniFile(dirPath, fileName);

                    //_dbConnection = new OleDbConnection(

                    //    String.Format(

                    //        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}\\;Extended Properties='text;HDR=Yes;FMT=Delimited'",

                    //        dirPath));

                     string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt; Persist Security Info=False";

                   //  string strConnString= "Driver={Microsoft Text Driver (*.txt;*.csv)};Dbq="+dirPath+";Extensions=asc,csv,tab,txt;Persist Security Info=False";

                     System.Data.Odbc.OdbcConnection _dbConnection;

                     _dbConnection = new System.Data.Odbc.OdbcConnection(

                                    strConnString.Trim());

                    _dbConnection.Open();

                    strReaderQuery = CSV_QUERY + "[" + fileName + "]"; // kaf causes error when file name has spaces

After lot of research i found that, it was a problem with impersonation issue. I have set impersonation to true to fix some other problem and that was the issue here. When we read data from  excel file , these data will be stored in temp folder and these folders will be accessible only by ASPNET  account. So, if you use impersonation it will be overridden and that cause the issue. Just changed the impersonation to false and that solved the issue. Now the temp folder is having the proper authorization and i got rid of the issue. This is not only specific to reading excel file, whenever you read something in systems memory, make sure that you don't override the impersonation.

cascade delete - Deleting the foreign keys tables without giving the child table names.

Have you ever faced a situation where you need to delete all your master table data along with its corresponding transaction table records. I faced few days ago and got a solution to delete the records with foreign keys references. The below code worked for me for cascade delete, if its not working for you pls let me know with the error u get.


declare  @cTab varchar(255),  
 @cCol varchar(255),  
 @cRefTab varchar(255),  
 @cRefCol varchar(255),  
 @cFKName varchar(255),  
 @cSQL nvarchar(1000),  
 @cChildCriteria nvarchar(1000),  
 @iChildRows int  

 
DECLARE cFKey CURSOR LOCAL FOR  
SELECT SO1.name AS Tab,  
       SC1.name AS Col,
       SO2.name AS RefTab,  
       SC2.name AS RefCol,  
       FO.name AS FKName  
FROM dbo.sysforeignkeys FK    
INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid = SC1.id  
                              AND FK.fkey = SC1.colid  
INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid = SC2.id  
                              AND FK.rkey = SC2.colid  
INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid = SO1.id  
INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid = SO2.id  
INNER JOIN dbo.sysobjects FO ON FK.constid = FO.id  
WHERE SO2.Name = @cTableName  

OPEN cFKey  
FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
WHILE @@FETCH_STATUS = 0  
     BEGIN  
   SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +  
           @cRefTab +'] WHERE ' + @cCriteria + ')'  

     
   EXEC [sp_CascadeDelete] @cTab, @cChildCriteria  
   FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName  
     END  
Close cFKey
DeAllocate cFKey  

SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria  
EXEC sp_ExecuteSQL @cSQL

How to get a currently logged in username with Domain name using Javascript

I got a situation in my classic ASP application in which i need to restrict users to some pages based on their active directory username. Initially, i implemented this like, created a new asp page and their i got the logon user name and based on that user name i'm setting the session variables to set the permission. So, when a particular page is accessed (before that i have included my newly created authentication asp file), i check the session variable and let user to access the page.

   The problem with this approach is, My client don't want to add include file in each and every page where we need to check the condition. He wanted to include this file in global.asa. But the problem is, we cant include a asp file which has <% or %> derivatives. We can include only files which has only script functions.

So, i had to find a way to get the logged in username with scripts and finally i came up with a solution as below,


    var WinNetwork = new ActiveXObject("WScript.Network");
 
    Session("user") = WinNetwork.Domain ;


This works perfectly, but the problem is its retrieves only the user name, but not the domain name.

For example : lets say my domain name is  XYZ and my userid is Mathi, the above code returning my user name as "Mathi", instead of "XYZ/Mathi"


Well, i struggled a lot to find the user name with domain using javascript and finally end up with the below code.


    var wmi = GetObject("winmgmts:{impersonationLevel=Impersonate}\\\\.\\root\\cimv2");
    var query = "Select * From win32_computersystem";
    e = new Enumerator(wmi.ExecQuery(query));
    var data = e.item();

    var strUser = data.UserName;



this code returned the result as expected.