logo
down
shadow

Import data from Excel using SSIS without knowing the file name


Import data from Excel using SSIS without knowing the file name

By : Quintin Van Rooyen
Date : October 18 2020, 03:08 PM
may help you . I'm working on an SSIS package that will be used to import data from an Excel file into SQL Server. My current struggle is figuring out how to make the SSIS package bring in exactly one excel file without knowing the name of it beforehand. I have a directory that will contain between 0 and n excel files at the same time. I want to pull in only the file with the oldest creation time. Is this possible? , To create a dynamic file connection:
code :


Share : facebook icon twitter icon
Import data from Excel using SSIS without knowing sheet name

Import data from Excel using SSIS without knowing sheet name


By : user2544550
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I would script out the Worksheet name to a SSIS User Variable. If you are not opposed to inserting a script task into your SSIS package try this: (Based on link text )
code :
Excel.Application xlApp = new Excel.ApplicationClass();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("<Name of your excel app>.xls", 0, xlWorkBook true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Look up worksheet by index
Excel.Worksheet xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

user::worksheetname = xlWorkSheet.Name;

/* Do clean up.  Working with COM object */
New Excel file with different format want to import data to sql table using ssis

New Excel file with different format want to import data to sql table using ssis


By : pitylee
Date : March 29 2020, 07:55 AM
This might help you You may achieve this using a script task in your package. But consider using SSRS instead. I think this is what SSRS made for.
With SSRS you can make a report using BIDS or also you can create a local report with Visual Studio inside of an (e.g.) ASP.NET application. With the ASP.NET ReportViewer component you can visualize your report and export it to Excel as well. You can even export programmatically if you should do this.
Import big excel file using excel connection manager in SSIS

Import big excel file using excel connection manager in SSIS


By : Thet Htet San
Date : March 29 2020, 07:55 AM
it helps some times SSIS does not limit the number of rows it can import from any particular source (Unless you are using some sort of constraint). By default, the preview usually gives about 200 rows only and I dont know of a way to change it.
Although you can change the number of rows you can use to query the metadata to probably 10000 but for preview, it is pretty standard.
Import EXCEL with SSIS without knowing sheename

Import EXCEL with SSIS without knowing sheename


By : godsid
Date : March 29 2020, 07:55 AM
around this issue As I had mentioned earlier, the error does clearly suggested you have some non-declaration statements at the class level which is not valid.
Your code from the script task have some issues with the closing brace --
code :
public void Main()
        {
            // TODO: Add your code here

            string excelFile = null;
            string connectionString = null;
            OleDbConnection excelConnection = null;
            DataTable tablesInFile = null;
            int tableCount = 0;
            DataRow tableInFile = null;
            string currentTable = null;
            int tableIndex = 0;
            string[] excelTables = null;

            excelFile = Dts.Variables["User::BBGFilePath"].Value.ToString();

            //Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:\CESLtd\ELKAY\Reports\Work2\Book1.xls; Extended Properties = "EXCEL 8.0;HDR=YES";
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;HDR=YES";

            excelConnection = new OleDbConnection(connectionString);

            excelConnection.Open();
            tablesInFile = excelConnection.GetSchema("Tables");

            tableCount = tablesInFile.Rows.Count;
            excelTables = new string[tableCount];

            foreach (DataRow tableInFile_loopVariable in tablesInFile.Rows)
            {
                tableInFile = tableInFile_loopVariable;
                currentTable = tableInFile["TABLE_NAME"].ToString();
                excelTables[tableIndex] = currentTable;
                tableIndex += 1;
            }
        //} **commented this line now you are good to go**

            //Provide value to the shetename variable
            Dts.Variables["User::SheetName"].Value = excelTables[0];



            //Display file name
            string strMessage = Dts.Variables["User::BBGFilePath"].Value.ToString();
            MessageBox.Show(strMessage);

            Dts.TaskResult = (int)ScriptResults.Success;

        }
SSIS: import data from excel to sql server database with file last modified date condition

SSIS: import data from excel to sql server database with file last modified date condition


By : user3164491
Date : March 29 2020, 07:55 AM
I hope this helps you . This link will give you step by step answer. You need to take care about flat file connection to excel file connection and join based on last modified date column.
Related Posts Related Posts :
  • Import large database from bacpac with SQL Server Management Studio
  • Error in specify relationship in data source view of ssas
  • I am having trouble using contains in sql server 2008 and doesn't show any output if my text has "for" or &quo
  • OSQL command to stop the services
  • CLR Function publish.sql file
  • Performance impact of Lookup in SSRS Report Builder
  • Add character to the left of the String
  • Log column constraint violations
  • Concatenate many rows into one
  • Create #temp using SELECT INTO with desired data type
  • SQL server concat SP output with multiple columns into one string in select query
  • Updating a date record and getting: Operand type clash: date is incompatible with int
  • Executing thousands of SQLServer queries at one time
  • SQL Best Practices for Identity value hard coding
  • How make sqlcmd hold file access rights on linux?
  • How to insert new record in AdventureWorks2016 which uses BusinessIdentityId
  • Conditional logic in T-SQL select statement
  • Why does query return a line when using a date filter when it shouldn't?
  • Altering MSSQL table protected by RLS (SSDT)
  • Execute SQL stored procedure from QueriesTableAdapter in DataSet at view design
  • DB changes doesn't reflect on Diagram
  • shadow
    Privacy Policy - Terms - Contact Us © soohba.com