SSIS-Multiple .xlsx files data insert into database but problem is three files in my folder s1,s2,s3 read and insert data but only s1 files data inserted in database three times so any solution please rplay me.
Actual my requirement is dynamically create table and read column because of source file data (columns) are increase –decrease. For e.g. – file s1 available 10 columns but file s2 15 columns so how to manage in insert data in database.Using ssis.
Putting aside the thought that this sounds like a terrible system design... SSIS does not allow you to create self-modifying packages. But you can create/modify packages using scripting. So my approach would be to do this. But no-one can help you with such limited information about what you want to do. Why do the files change? Do they have the same content across files? Do you need to process all of the file or just certain columns? As JohnM asked, what versions are you running? This might start you off:
http://stackoverflow.com/questions/4281237/ssis-flat-files-with-variable-column-numbers > but any body share email id so discussions Please don't do this - it prevents others from getting the benefit of any solutions proposed.
![alt text] : /storage/temp/2033-requirment.png Hello Sir, Sorry, Please check like this requirement. I am try successfully insert data in database, but two different files how to insert in database and multiple file data read and insert in database tables.
Ok, that's a bit more to go on. Firstly, instead of using a new table each time, I'd suggest creating a single staging table: create table dbo.MyLoadStaging ( Name varchar(200) , Age int , Residence varchar(200) , CityName varchar(200) , TVType decimal(2,1) , TypeValue decimal(2,1) ) **Note - changed table structure on edit.** Before we even consider doing this dynamically, we'll create a static package using a single file. This will allow you to understand how the data is moving through the process. - Create a new SSIS package. - Create an Excel connection manager using one of your load files. - Add an Excel Source component to the data flow using this CM. - Add an unpivot transformation and link the Excel Source to it. - Configure the unpivot to give you the following columns: Name | Age | Residence | CityWithTVType | TypeValue (Using unpivot allows us to convert any number of input columns to a discrete set of output columns. See this for more info:
https://msdn.microsoft.com/en-us/library/ms141723.aspx) The data will be transformed into something like this: assuming input of: Name | Age | Residence | Mumbai.TVR | Mumbai.CRP |
Mumbai.TV Suresh | 30 | Bangaloar | 0.1 | 0.2 | 0.3 output looks like: Name | Age | Residence | CityWithTVType | TypeValue Suresh | 30 | Bangaloar | Mumbai.TVR | 0.1 Suresh | 30 | Bangaloar | Mumbai.CRP | 0.2 Suresh | 30 | Bangaloar |
Mumbai.TV | 0.3 - Add a Derived Column transformation and link the unpivot transformation to it. Split the CityWithTVType to two distinct columns, City and TVType. - Create a SQL connection manager that points to your staging table. - Add a SQL destination using this CM and link the derived column transformation to it. This will transfer the file to the staging table. Once you can successfully do this, I'm happy to give you some pointers on dynamically changing the package to deal with different formats. You need to answer the following questions: ---------------------------------------- - What version of SSIS and SQL Server are you running? - What is your level of ability with scripting languages? Edit: Buffoonery.
Hi iainrobertson, Thank You But,This Is only one file Output data multiple files data insert in database and all files **columns** increase/decrease that time how to possible to insert data in database. I am try to insert data in 2 different tables. I am all ready create package static columns using in multiple files and inset in to database successfully done but problem is only one files data insert multiple time for e.g. s1 and s2 two excel files so read two files but insert only s1 files data two times. how to avoid this? and How to manage increase decrease columns? assuming input of: Name | Age | Residence | Mumbai.TVR | Mumbai.CRP |
Mumbai.TV Suresh | 30 | Bangaloar | 0.1 | 0.2 | 0.3 output looks like: Table 1 Name | Age | Residence | CityName | TVR |CRP | TV |seqID Suresh | 30 | Bangaloar | Mumbai | 0.1 | 0.2 | 0.3|1 Table2 Id| CityName | TVR |CRP | TV|SeqiD 1| Mumbai | 0.1 | 0.2 | 0.3|1 Currently I am Using VS 2010 means Version 4.0 SSIS scripting knowledge in learning phase.. Please give me solutions. Thank You
Hello Sir, Please help me for ssis related. Point 1: I am read .xlsx file (File Name s1) with 3 columns(col1,col2,col3) insert data into sql database table. Point 2: file s2 5 columns available same columns are found in file then avoid only remaining columns are create and insert into database table. Please replay. Example: Requirment shown below... File S1 Name Address Age Suresh Mumbai 30 Jay Pune 28 File S2 Name Address Age Language Gender Suresh Mumbai 30 English Male Jaya Pune 28 English Female Output Name Address Age Language Gender Suresh Mumbai 30 English Male Jaya Pune 28 English Female