asked
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.
Forgive me, just to clarify, you want to dynamically create a destination table based of the columns in the source and then insert the data from the source into the newly created table? Is that correct? Are both the source and destination RDBMS systems SQL Server? If so, what versions?
Please Help Me urgently..... Any body not help me.... but any body share email id so discussions.... and then share my demo app with email
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][1] [1]: /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.
Hello sir, Table1 & Table2 are constantly changing because of files column are Increase and decrease but i am create table like 5 columns but my 2nd file data columns are 7 how to manage.
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
I don't wish to be rude, but I can't help if you don't listen. Good luck with it.
I'm trying to. But I'm all out of magic bullets, so we'll just have to do it the traditional step by step way. In my post, I created a single staging table to handle all inputs, regardless of what columns they have. Have you created this? Then you need to create a (yes, STATIC) package to load to this table. Once you have these in place, we can talk about how to make it dynamic.
Hello Sir, Sorry but Any solution for my question? 2nd problem i am read multiple .xlsx files (e.g. 2 files) data and insert database using foreach loop but read 1st file but insert 2 times 1st file data how to possible 1st file data insert after 2nd file data insert correctly. i am try but problem is ,i am extra columns add trough script that time problem fetch but no extra columns add that time not any problem both files data insert properly. i am surfing in net same solution but not run properly so please suggest (solution) me. ASAP Thanks Suresh
Thank You , i am understand you can't help me and "ask sql server central" team also Not help of me..... Relay thank You very much discuss with me....
Hello Sir, Please help me Using SSIS How to possible Multiple .xlsx files Merge in one file(.xlsx) please tel me hows it?
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
20 People are following this question.
