December 27, 2012 at 5:07 pm
Greetings all.
I am learning SSIS and having great fun with Sequence Containers, Control flows and Data flows. I believe I have the general idea, so I am attempting to load 42 columns from an Excel spreadsheet into a table in SQL 2008 Std. R2 64-bit with corresponding columns. No columns are skipped or rearranged; column headers are in Row1, or course.
I have already discovered the issue with Excel and 64-bit systems, so I have set the package correctly to use a 32-bit process on my 64-bit machine. I am running as an admin when testing.
I can even get most of my SQL tasks to run correctly. However, the data flow processes that actually handle the 42 columns are only moving the first five (5) columns of data in 9216 rows...and the remaining 37 columns out to the end of the data row have been loaded with NULLS. There is data in the original spreadsheet, but I'll be darned if I can find out why only five columns are coming over in the data flow.
Without inserting a bunch of screen shots, has anyone any ideas why only part of a spreadsheet would load?...for 9216 rows? I use an Excel source task, a data conversion task ( to get columns set to correct sizes), and an OLE destination pointing to my SQL table. I have tried running without the data conversion task with no difference (just bigger columns). I don't know if there is some other system setting that I'm not aware of, so I'm throwing in the towel for today, and putting this out to see if anyone can direct me in my search for answers. Thanks. JT Nelson
December 27, 2012 at 6:01 pm
nelsonj-902869 (12/27/2012)
Greetings all.I am learning SSIS and having great fun with Sequence Containers, Control flows and Data flows. I believe I have the general idea, so I am attempting to load 42 columns from an Excel spreadsheet into a table in SQL 2008 Std. R2 64-bit with corresponding columns. No columns are skipped or rearranged; column headers are in Row1, or course.
I have already discovered the issue with Excel and 64-bit systems, so I have set the package correctly to use a 32-bit process on my 64-bit machine. I am running as an admin when testing.
I can even get most of my SQL tasks to run correctly. However, the data flow processes that actually handle the 42 columns are only moving the first five (5) columns of data in 9216 rows...and the remaining 37 columns out to the end of the data row have been loaded with NULLS. There is data in the original spreadsheet, but I'll be darned if I can find out why only five columns are coming over in the data flow.
Without inserting a bunch of screen shots, has anyone any ideas why only part of a spreadsheet would load?...for 9216 rows? I use an Excel source task, a data conversion task ( to get columns set to correct sizes), and an OLE destination pointing to my SQL table. I have tried running without the data conversion task with no difference (just bigger columns). I don't know if there is some other system setting that I'm not aware of, so I'm throwing in the towel for today, and putting this out to see if anyone can direct me in my search for answers. Thanks. JT Nelson
You could check if all the input columns are mapped to the output columns in the OLEDB destination...
December 28, 2012 at 9:07 am
@SQLFRNDZ (12/27/2012)
You could check if all the input columns are mapped to the output columns in the OLEDB destination...
Yup, all column mappings are correctly intersected with SQL destination table columns. I tried the SQL Source Destination, but that task abends with: "409 error: Unable to prepare the SSIS bulk insert for data insertion" and the task fails.... so I went back to an OLE DB Destination, which gives me only the five columns. I'm researching the 409 error this morning.
December 28, 2012 at 12:17 pm
Truely weird....
I now have a two step process in SSIS that runs to normal completion - no errors... but the Data Flow parts will only transfer columns 1 thru 5 with data for 9206 rows...Columns 6 thru 42 are all NULLED out, even though the Excel source has data in 99% of the columns on every row.
For the life of me I can not determine why this Excel transfer is only sending the first five columns to SQL....for all 9206 rows!! :crazy:
I have applied the settings for running SSIS/Excel on 64-bit systems. I have changed registry settings so the system will check all rows of the Excel member for mixed data types. There has to be some other setting that I am missing. Its like the Data Flow gets to column 6 and begins to NULL everything out!!
Ar-r-r-r-rg-g-g-g-g!!!
December 28, 2012 at 12:26 pm
Attach the Excel sheet to this thread and I'll have a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 28, 2012 at 12:44 pm
It's a monster...9200 rows. How about a sample of 200 rows?
December 28, 2012 at 1:38 pm
Cool. It won't be right away, but hopefully sometime over the weekend.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 28, 2012 at 11:07 pm
I was able to load the data as it is in EXCEL. All the columns are loaded. I do not see any issue. I have sql server 2008 R2 on my machine
December 29, 2012 at 4:36 am
SatishAyyar (12/28/2012)
I was able to load the data as it is in EXCEL. All the columns are loaded. I do not see any issue. I have sql server 2008 R2 on my machine
Did you check the table in your database to see if column 6 and beyond contained data?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 29, 2012 at 4:51 am
nelsonj-902869 (12/28/2012)
It's a monster...9200 rows. How about a sample of 200 rows?
Attached is a SSIS package that loaded all columns in your test file cleanly into a test database I created just for this. In hte package just change the connection managers to point to your Excel file and a test database of your choosing and see if it works on your end. Here is the table table definition SSIS generated based on what the Excel driver was seeing that you'll want to create in your test database:
/****** Object: Table [dbo].[Report 1] Script Date: 12/29/2012 04:39:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Report 1](
[Case Def Name] [nvarchar](255) NULL,
[Case Def Mailing Address] [nvarchar](255) NULL,
[Def Mailing Address City] [nvarchar](255) NULL,
[Case Def Mailing Address State] [nvarchar](255) NULL,
[Case Def Mailing Address Zip Code] [nvarchar](255) NULL,
[Bail Amount] [money] NULL,
[Warrant Exp] [nvarchar](255) NULL,
[Case Number] [nvarchar](255) NULL,
[Sex] [nvarchar](255) NULL,
[Race] [nvarchar](255) NULL,
[Eyes] [nvarchar](255) NULL,
[Hair] [nvarchar](255) NULL,
[DOB] [nvarchar](255) NULL,
[Height] [nvarchar](255) NULL,
[Weight] [float] NULL,
[Place of Emp] [nvarchar](255) NULL,
[Operator Lic No] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[Exp] [nvarchar](255) NULL,
[Additional Id Data] [nvarchar](255) NULL,
[Veh Year] [float] NULL,
[Veh Make] [nvarchar](255) NULL,
[Veh Type] [nvarchar](255) NULL,
[Veh Color] [nvarchar](255) NULL,
[Lic Plate No] [nvarchar](255) NULL,
[Lic Plate State] [nvarchar](255) NULL,
[Lic Plate Exp] [nvarchar](255) NULL,
[Orig Agency] [nvarchar](255) NULL,
[Orig Agency Case No] [nvarchar](255) NULL,
[Officers No] [nvarchar](255) NULL,
[Viol Date] [nvarchar](255) NULL,
[Complaintant] [nvarchar](255) NULL,
[Citation No] [nvarchar](255) NULL,
[Charge 1] [nvarchar](255) NULL,
[Charge 1RCW] [nvarchar](255) NULL,
[Charge 2] [nvarchar](255) NULL,
[Charge 2RCW] [nvarchar](255) NULL,
[Charge 3] [nvarchar](255) NULL,
[Charge 3RCW] [nvarchar](255) NULL,
[Charge 4] [nvarchar](255) NULL,
[Charge 4RCW] [nvarchar](255) NULL,
[F42] [nvarchar](255) NULL
) ON [PRIMARY]
GO
Hopefully this will get you onto a working model, a proof of concept if you will, and you can build off it to get a working solution.
I ran my test with everything running on a 32-bit Windows 7 machine:
- BIDS 2008
- SSIS build 10.50.4000.0
- SQL 2008 R2 instance w/ SP2 (10.50.4000.0)
- built-in Excel Source in SSIS package w/ connection string Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\VBOXSVR\Downloads\TestExcel.xls;Extended Properties="Excel 8.0;HDR=YES";
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 29, 2012 at 8:33 am
Yes the table was loaded with all the data that is in excel. Try to create a new package and see if it works
December 29, 2012 at 8:52 am
SatishAyyar (12/29/2012)
Yes the table was loaded with all the data that is in excel. Try to create a new package and see if it works
I did, and was able to load the data as well. See my last post. The package is attached. I did not require the Data Conversion Transformation due to the destination table column data types matching the source (DT_WSTR to NVARCHAR).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 29, 2012 at 9:09 am
I din't needed the dataconversion transformation either. But I was initially verifying with just the excel source and dataconversion and added a data viewer to see if the data is passing through correctly. And then I connected to OLEDB destination. The dataconversion step is empty in the image i attached.
I don't understand why the excel is not correctly loading for him. Some times I see some weird problems
and I create a new package or restart BIDS and that would take care of it.
December 31, 2012 at 8:03 am
Hi all. I appreciate everyone considering my issue. However, I have been able to download the data into columns of nvarchar (255), but as a database designer from way back, I have a real problem with all 42 columns being (255) characters in length, especially when a good majority of the columns have less than 15 characters of data in them.
So, I included a data conversion task to reduce the column lengths down to bare max size. My SQL definition is attached for you to see. I know the issue is within the data conversion, but why convert only the first five columns and NOTHING else??!? If I could figure out how to get the data conversion task loaded here I would show it, but VS 2008 is not cooperating with me.
December 31, 2012 at 11:49 am
I may as well give up.
I went back into VS 2008 and deleted all the package attempts and I started off fresh again. I completely rebuilt the process - used Excel Connection Mgr for the connection, reset the 64bit property to FALSE for the package so the Excel would work, rebuilt the OLE DB connection to SQL; set up the Data Flow to use the Excel as input, did NOT do a Data Conversion task; went directly to an OLE DB destination using an SQL table as the final destination.
Ran the package - ran to completion - all green--went in to SQL and reviewed the destination table -
only the first five columns loaded for 9219 rows (today's count.) All other columns have NULLS in them. It must be my version/install of VS 2008....... I just don't know what else to try. Something in the VS setup or the package setup must be doing this.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply