question

9860855972 avatar image
9860855972 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.
columncolumns
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@9869855972 - No, it means that I don't have enough information to help you find a solution to your question. As mentioned by @raadee, help us help you by giving us as much details as you possibly can. This will help us to find a proper solution for you.
1 Like 1 ·
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?
0 Likes 0 ·
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
0 Likes 0 ·
Help yourself by answering above questions from JohnM.
0 Likes 0 ·
Hello JohnM, Means r u not answering(solve) above question.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

9860855972 avatar image
9860855972 answered
Hello Sir, I am provide attached file like my requirement for input and output structure. You can check and try to dynamic as possible and as possible static.Using SSIS.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Just an FYI, there wasn't anything attached.
0 Likes 0 ·
9860855972 avatar image
9860855972 answered
![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.

requirment.png (37.0 KiB)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You mentioned that the output is dynamic, what is the deciding factor to know what data is going to which destination? Table1 & Table2 are constantly changing?
0 Likes 0 ·
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.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

9860855972 avatar image
9860855972 answered
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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I don't wish to be rude, but I can't help if you don't listen. Good luck with it.
1 Like 1 ·
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.
0 Likes 0 ·
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
0 Likes 0 ·
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....
0 Likes 0 ·
9860855972 avatar image
9860855972 answered
Hello Sir, Please help me Using SSIS How to possible Multiple .xlsx files Merge in one file(.xlsx) please tel me hows it?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

9860855972 avatar image
9860855972 answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.