June 20, 2014 at 11:19 pm
Hi,
I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.
Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,
Thanks in advance.
June 20, 2014 at 11:46 pm
ykonline (6/20/2014)
Hi,I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.
Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,
Thanks in advance.
The simplest is to always import the maximum number of columns and ignore the null columns in the stage area. Other options are i.e. programmatically building the import, one package for each document type triggered from a master package which detects the type etc..
😎
June 21, 2014 at 3:50 pm
ykonline (6/20/2014)
Hi,I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.
Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,
Thanks in advance.
Does that also mean that the columns present in a 10 column spreadsheet could be in different positions for the 12 column spreadsheets?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2014 at 3:57 pm
Jeff Moden (6/21/2014)
ykonline (6/20/2014)
Hi,I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.
Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,
Thanks in advance.
Does that also mean that the columns present in a 10 column spreadsheet could be in different positions for the 12 column spreadsheets?
Missing here is the information on if and how one import relates to another. For what it's worth, those are, in my experience the first signs of a fully meta-data driven ETL requirements. And that is starting with a toothpick and ending up with a forest.
😎
June 27, 2014 at 11:46 pm
No, Columns positions will be same but we may receive new columns that
may not be exists in previous sheet which we received.
For Example,
First time, we receive the Sheet with C1,C2,C3 columns.
When we receive sheet for Second time, it may contains columns like C1,C2,C3,C4
and for the third time we may receive with C1,C2,C3,C4,C5,C6 etc.,
Thanks in advance
June 28, 2014 at 12:20 pm
ykonline (6/27/2014)
No, Columns positions will be same but we may receive new columns thatmay not be exists in previous sheet which we received.
For Example,
First time, we receive the Sheet with C1,C2,C3 columns.
When we receive sheet for Second time, it may contains columns like C1,C2,C3,C4
and for the third time we may receive with C1,C2,C3,C4,C5,C6 etc.,
Thanks in advance
What are the actual column names in this example, please? I ask because there may be a post import optimization to help with normalization if they're named right.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2014 at 12:13 am
Please find the details below
Date CAD CZK EUR
1/1/20101.052018.34750.6977
1/2/20101.053218.39850.6957
1/3/20101.051718.33600.6982
1/4/20101.037718.16240.6935
1/5/20101.037118.25410.6943
Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,
July 5, 2014 at 12:32 am
ykonline (7/5/2014)
Please find the details belowDate CAD CZK EUR
1/1/20101.052018.34750.6977
1/2/20101.053218.39850.6957
1/3/20101.051718.33600.6982
1/4/20101.037718.16240.6935
1/5/20101.037118.25410.6943
Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,
ykonline (6/20/2014)
Hi,I want to insert data periodically from an excel file into a table using a SSIS. But each time the no. of columns in excel may vary.
Suppose, the 1st time we receive excel file may contain 10 columns the second time it may contains 12 columns etc.,
Thanks in advance.
Seems simple enough. What does the target table (where you want to insert from an excel file into a table) look like?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2014 at 1:11 am
Below is my target table and data should be inserted like below.
Date RepCurr Earning
1/1/2010CAD1.0520
1/1/2010CZK18.3475
1/1/2010EUR0.6977
1/2/2010CAD1.0532
1/2/2010CZK18.3985
1/2/2010EUR0.6957
1/3/2010CAD1.0517
1/3/2010CZK18.3360
1/3/2010EUR0.6982
1/4/2010CAD1.0377
1/4/2010CZK18.1624
1/4/2010EUR0.6935
1/5/2010CAD1.0371
1/5/2010CZK18.2541
1/5/2010EUR0.6943
1/1/2010USD0.9506
1/1/2010USD0.0545
1/1/2010USD1.4333
1/2/2010USD0.9495
1/2/2010USD0.0544
1/2/2010USD1.4374
1/3/2010USD0.9508
1/3/2010USD0.0545
1/3/2010USD1.4323
1/4/2010USD0.9637
1/4/2010USD0.0551
1/4/2010USD1.4420
1/5/2010USD0.9642
1/5/2010USD0.0548
1/5/2010USD1.4403
Thanks in Advance!!!
July 6, 2014 at 1:23 pm
ykonline (7/5/2014)
Please find the details belowDate CAD CZK EUR
1/1/20101.052018.34750.6977
1/2/20101.053218.39850.6957
1/3/20101.051718.33600.6982
1/4/20101.037718.16240.6935
1/5/20101.037118.25410.6943
Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,
Just to set some expectations, if knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mini-bike through a matchbox. Part of the reason for that is that I've not needed to use SSIS to do such things. Instead, I do it all in T-SQL.
Doing the required dynamic unpivot is simple but we need to get the data into a table where we can actually work on it.
That leads me to a couple of questions before I can help (others that know SSIS, please DO jump in)...
1. Is there a way that SSIS can automatically create a table with the right number of columns (no matter how many) from a given spreadsheet? If so, this will be an absolute cake walk. If not, we'll have to bake the cake, first.
2. If the answer to question #1 above is "No, SSIS can't automatically create a table with the right number of columns for a given spreadsheet", then are you allowed to use the ACE drivers and OPENROWSET?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 6, 2014 at 1:43 pm
Jeff Moden (7/6/2014)
ykonline (7/5/2014)
Please find the details belowDate CAD CZK EUR
1/1/20101.052018.34750.6977
1/2/20101.053218.39850.6957
1/3/20101.051718.33600.6982
1/4/20101.037718.16240.6935
1/5/20101.037118.25410.6943
Sometimes we may receive additional(new) columns like GBP, MXN, MYR etc.,
Just to set some expectations, if knowledge of SSIS were gasoline, I wouldn't have enough to drive a sugar-ant's mini-bike through a matchbox. Part of the reason for that is that I've not needed to use SSIS to do such things. Instead, I do it all in T-SQL.
Doing the required dynamic unpivot is simple but we need to get the data into a table where we can actually work on it.
That leads me to a couple of questions before I can help (others that know SSIS, please DO jump in)...
1. Is there a way that SSIS can automatically create a table with the right number of columns (no matter how many) from a given spreadsheet? If so, this will be an absolute cake walk. If not, we'll have to bake the cake, first.
2. If the answer to question #1 above is "No, SSIS can't automatically create a table with the right number of columns for a given spreadsheet", then are you allowed to use the ACE drivers and OPENROWSET?
There are rather straight forward ways of doing this in SSIS, i.e. a package reads the first portion of the file, determines the structure, creates a table or a package to create the table and creates another package for the transfer. But although one can do all kinds of stuff with one technology it may not be the right approach. In other words, it all depends on the environment, file location etc.. For example, if the files are accessible from the SQL Server, wrapping an OPENROWSET in some dynamic sql might is an option, maybe splitting the roles, use SSIS to transfer the files from a remote location and then fire the code on the server to digest them. In fact, I have found that the combination of the two is simple to create and maintain.
BTW, one of my favourite methods is to read the file one line in a row and use a splitter:Whistling: to parse it, great for multi document files.
So here is the question, can you describe the environment, the data source (where are the files coming from), the application/SSIS server and it's relation to the SQL Server etc.?
😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply