Excel Source Trying To Import Additional Columns

Apr 17, 2008

Hi

I have an excel source which is a 41 column sheet. The excel filepath is stored in a table and captured into a variable. The excel source import is contained within a foreach loop and will loop through each file and continue until all the excel files are processed. It works fine until it gets to the last file. The import then fails with the following error:

The column "F42" needs to be added to the external metadata column collection.
The column "F43" needs to be added to the external metadata column collection.
The column "F44" needs to be added to the external metadata column collection.
The column "F45" needs to be added to the external metadata column collection.
The column "F46" needs to be added to the external metadata column collection.
The column "F47" needs to be added to the external metadata column collection.

Now when i open the excel sheet and hit CTRL+END the cursor goes to a column 6 to the right of the last column with data in it, effectively column 47 where column 41 is the end of my data.

I guess that the jet engine is trying to import these additional columns but because i am not expecting them there is no destination set up for them in the OLEDB destination and susequently the metadata needs to be added. I do not want to do this as these are excel files originating from the client and i cannot control how many additional columns they are going to "add".

Does anyone have any ideas as to how i can solve this? Is there a way of identifying the last column with data and only importing those columns?

Thanks in advance for any help or experience of this issue

View 2 Replies


ADVERTISEMENT

SSIS Wizard Cannot Import Text Columns Longer Then 255 Using Excel Source

Nov 29, 2006

(Applies to SQLServer 2005 SP1)

We have found that using the SSIS "Import and Export Wizard" using the "Microsoft Excel" data source that there appears to be a maximum column length of 255 characters for any row.

Even when defining the destination table columns as nvarchar(4000), the wizard fails with the errors shown below.

We have found no workaround except manually changing the imput data. There doesn't appear to be any "Advanced" options for the Excel importer as there are for the flat-text importer. So, no question here, just posting the bug so that *next* time someone searches the web for an answer, this post comes up

MessagesError 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)


edit: After searching further this is documented under "Excel Source"
in BOL which provides a registry-based workaround.  I guess the issue
is that the wizard considers truncation to be  a 'fail' case and
there's no easy way to override this behaviour, specify the column
types nor determine which line is in error)



Truncated text. When the driver determines that an Excel column contains
text data, the driver selects the data type (string or memo) based on the
longest value that it samples. If the driver does not discover any values longer
than 255 characters in the rows that it samples, it treats the column as a
255-character string column instead of a memo column. Therefore, values longer
than 255 characters may be truncated. To import data from a memo column without
truncation, you must make sure that the memo column in at least one of the
sampled rows contains a value longer than 255 characters, or you must increase
the number of rows sampled by the driver to include such a row. You can increase
the number of rows sampled by increasing the value of TypeGuessRows under
the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel registry
key.
)

View 21 Replies View Related

Integration Services :: Excel Column Turns To Blank / NULL While Import Using SSIS Excel Source 2008

Jul 6, 2015

While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with

HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel

1.Change the Value  of the Row TypeGuessRows from 8 (Default value) to 0  and ImportMixedType = text

• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel

1.Change the Value  of the Row TypeGuessRows from 8 (Default value) to 0  and ImportMixedType = text

the connection string of the excel

UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"

by doing the above setting also , the column is coming as null from excel source even though there is data in excel.

View 2 Replies View Related

Using A Excel Source To Get The Data From An Excel File Gets Null Values For A Couple Columns

Nov 19, 2007

I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.

Thanks,
Manisha

View 4 Replies View Related

Cannot Import Excel Source File With Empty Last Column

Oct 23, 2007

Hi All,

I have a particular issue that has been causing me some problems for a while. I have an SSIS package that imports an excel file into my database, and then performs various data manipulation that I won't go into. The problem I am having is at the import end. The excel source file I am working on is provided to me by my client. It is a fixed format and doesn't change, it contains a header row and there are 32 headings. The trouble I am having is that quite often, the last column is empty, i.e. it contains no data. The header is still there, but theres no data underneath. When I try to import this file using my SSIS package it fails, and complains about needing to remove the metadata for this final column from the External Columns list (VS_NEEDSNEWMETADATA). When I try to preview this file in the properties of the Excel Data Source, the last column does not exist. It's as if it's determining that as there is no data in that final column, that it's unnecessary and not part of the data set, even though it has a header.

Now I've done a bit of research, and found cases that a sort of like mine, I know that the excel file has the first 8 records sampled to determine the data format. This problem suggested to use the IMEX=1 extension in the connection string, which didn't help. I also discovered that when using flat files, if you have odd numbers of columns in your comma seperated list there can be problems. But neither of these issues seem to match the issue I'm facing.

Has ANYONE had a similar problem to me, and can anyone offer any kind of assistance regarding what I need to do to import an excel file that may or may not have data in the final column?

Thanks in advance,

Paul

View 1 Replies View Related

Excel Source With Optional Columns

Jan 31, 2007

Hi:

I use a SSIS package to loop thro a folder and load data from multiple excel files to a SQL2005 table. Works fine except when an excel has a missing col.

Col names in xls are always a subset of col names in the table. The missing cols are random, else I would just have made another package:-)

Once a missing column is found, I get runtime and design time errors, and metadata problems. How can a get SSIS to ignore missing columns?

TIA





View 3 Replies View Related

Import Excel Data From User-selected Source File

Dec 4, 2007

Is it possible to import data from an Excel spreadsheet using OPENROWSET or OPENDATASOURCE without having to explicitly define the filepath of the source file? Currently, I have this piece of code within a sproc:


INSERT INTO [dbo].[ProductionRequirementDetail]

([ProductionRequirementHeaderID], [SKU], [Quantity])

SELECT @ProductionRequirementHeaderID,

[SKU],

[LAMPS]

FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0; Database=C:WeeklySchedule.xls', 'SELECT * FROM [Master$C5:Q65536]') AS XL

LEFT JOIN [dbo].[PartMaster] ON (RIGHT([XL].[CODE], 7) = [PartMaster].[SKU])

WHERE [SKU] IS NOT NULL

AND [CODE] IS NOT NULL

AND [LAMPS] IS NOT NULL

AND [LAMPS] > 0

AND [LampTypeID] = @LampTypeID




I would like to remove the hardcoded reference 'Database=C:WeeklySchedule.xls' and replace it with a parameter for the filepath. Is this possible? This is in SQL Server 2000. Also, if there is a way to do this with DTS I'd be open to doing it that way too.

View 8 Replies View Related

Question About Excel Source - How To Modify Columns??

Aug 22, 2007



Hi,

I have a package that uses an Excel file source. There appears to be no place to modify the column data types as you can with a flat file manager. As such, the source columns do not match the columns in the database.

I believe I must be overlooking something here.

Can someone please tell me how I can modify the Excel column datatypes?

Thanks

View 7 Replies View Related

How To Import Data From Excel Spreadsheet With More Than 255 Columns!

Nov 24, 2007



Hi

I get the following error when I use SQL Server 2005 Import/Export wizard to extract more than 255 columns from an excel file;

TITLE: SQL Server Import and Export Wizard
------------------------------
The preview data could not be retrieved.
------------------------------
ADDITIONAL INFORMATION:
Too many fields defined. (Microsoft JET Database Engine)
------------------------------
BUTTONS:
OK
------------------------------


DOES ANYONE KNOWS THE WORKAROUND?

Early Thanks,
Salman Shehbaz.

View 1 Replies View Related

SQL Server 2014 :: Import Data From Excel To Table - Null Instead Of 0 In Columns

Oct 29, 2013

I have a excel sheet with some data and blank columns. I have a ssis package using to import data from excel to sql table. For blank excel columns it is importing as null instead i want to show them as '0'. If data comes in it should update the data.

View 8 Replies View Related

Substring - Separating To 5 Additional Columns

Jun 6, 2014

I have data as below:

columnA
D7 330 4/13/2014 0:0:0 KUL PVG 4/13/2014 18:35:0 4/13/2014 19:15:0 4/13/2014 23:45:0 4/14/2014 0:45:0

How can I separate it to 5 additional columns?

columnB: D7 330 4/13/2014 0:0:0
columnC: KUL PVG 4/13/2014 18:35:0
columnD: 4/13/2014 19:15:0
columnE: 4/13/2014 23:45:0
columnF: 4/14/2014 0:45:0

View 7 Replies View Related

Additional Subtotal Column And Row When Exported To Excel

Aug 23, 2007

Hi, there,

When I view the report on the web, everything is fine until I exported the report to Excel. Additional column and row without heading appear in the exported Excel report. I have no idea, where do these row and column come from as I don't have these additional row and column in the designer. These additional row and column contain the subtotal.

How do I get rid of these additional row and column?

Thank you.

Regards,
Yong Hwee

View 3 Replies View Related

Import Data From Excel-Sheet Via OleDb In VB.Net - How To Get A Columns Data As String?

Oct 25, 2007

Hello,

i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:







Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next




it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.

So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?

Thanks for Reading

- Pierre from Berlin


[seems i got redirected into the wrong forum, please move into the correct one]

View 1 Replies View Related

Transactional Replication With Additional Columns In Destination Table

Jul 15, 2007

In Transactional Replication -while Publisher, Distributer & Subscriber are on same instance- can I have additional columns in destination Table & set "Action if name is in use" for related Article to "Keep existing object unchanged"?

I do this but even Snapshot is not transfered to destination table!!

View 5 Replies View Related

Reporting Services :: Report Builder 3.0 - Additional Columns Sorted After Values

May 12, 2014

I'm somewhat new to report builder and have been trying to recreate a report previously created in an Excel Pivot Table. I'm encountering an issue arranging the data the way it's arranged in Excel. 

Specifically, I would like the values column to precede an additional column.  

Until I can post pictures I'll have to try and mock it:

COLUMNS
Values
Results (my data I want as a 2nd column)

I can't figure out how to get report builder to do it the same way. Whenever I add the 'Result' data as a column it always appears on top. I'm guessing what I need to do is somehow get result set as a child of the first Static group, but I'm unsure how to do that.

View 4 Replies View Related

Integration Services :: Can't Import Excel 2013 Using SSMS Import Wizard (2008 R2)

Jul 29, 2015

I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard.  When pointed to the spreadsheet ("choose a data source")  the Import Wizard returns this error:

"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)

How can I address that issue? (e.g. Where is this provider and how do I install it?)

View 2 Replies View Related

How To Pass The Excel Sheet Names To The Excel Source Control Through Variables

Feb 22, 2006

I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.

For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".

And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.

So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.







Thanks & Regards,

Prakash Srinivasan

View 3 Replies View Related

Problem With Retreving A Excel Data Through Excel Source Component.

Sep 18, 2007



Hello,

I have a problem with retreving a excel data through excel source component.

I have source component as Excel Source which will connect to my .xls sheet.
To retrieve the values from the sheet i am using a query as,
"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"

The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..
While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

TITLE: Microsoft Visual Studio
------------------------------
There was an error displaying the preview.
------------------------------
ADDITIONAL INFORMATION:
Undefined function 'Convert' in expression. (Microsoft JET Database Engine)


Is there any other function to change the format of the cell or i need to some thing else
Please help me how to solve this issue.

View 6 Replies View Related

SSIS - DataFlowTask - Excel Source - Dynamic Excel Template

Mar 13, 2008



Hi,

I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database

I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..

In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically

Please suggest how could i accomplish this


Regards,
Kalyan

View 8 Replies View Related

How Do Create Table From Excel (based On Excel Column Name) And Import Data From It?

Jun 14, 2006

l've the following situation,

l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.

So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.


Thanks.

Regards,
Yong Boon, Lim


p/s : The excel header is at the row 7.

View 3 Replies View Related

Fuzzy Lookup Error When Adding Additional Lookup Columns

Sep 26, 2007

I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.

It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:

Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.

These errors occur regardless of what columns I am attempting to add to the lookup list.

I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.

Any ideas on what else could be causing this?

View 4 Replies View Related

Openquery Insert With Additional Columns For Manual Insert.

Apr 24, 2008



I am currently using openquery to insert data into a SQL 2000 database from a Lotus Notes database. The Lotus database is a linked server with a datasource named CLE_CARS_SF. My SQL table is called Webcases.

The query below works well because the table's columns are even in both databases:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')


I am moving this over to SQL 2005. The query works well, but I want to add a column to the Webcases SQL database and manually insert a value along with the openquery values.

My insert statement above no longer works because the column numbers don't match.

In a nutshell I would like a way to combine the following queries:

Insert into Webcases select * from openquery(CLE_CARS_SF,
'Select * from Web_Cases')

Insert into Webcases (insurancetype) Values ('SF')


--insurancetype is the new column.

View 9 Replies View Related

OLE DB Source To Flat File Destintation Using Fixed Width Columns - Determining Source Column Width

Feb 13, 2007

Hi,

I am trying to create a program that transfers tables to flat files.
At this point in time, I have suceeded in created one that creates delimited files.

However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.

Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.

I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.

View 5 Replies View Related

Import Excel Via Excel Command

Jan 24, 2008



Hi
I want to import excel in SSIS via sql command and change the data types. can anyone guide me on this or give me some sample command on how to change datatypes explicitly.

View 1 Replies View Related

Certain Numeric Fields Not Read From The Excel File When Using A Excel File Source.

Jul 20, 2006

I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.

All the other content from the excel file is coming thru except for the 2 numeric fields.

I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.

Any inputs on getting this addressed will be much appreciated.

Thanks,

Manisha

View 5 Replies View Related

Data Import Excel XML Defaults Which File Governs Excel Defaults?

Jul 21, 2015

I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spread sheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located

C:Program FilesMicrosoft SQL Server100DTSMappingFiles

Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10?

View 6 Replies View Related

IMPORT DATA FROM XML SOURCE TO SQL

Mar 2, 2006

Hello,

I'm trying to import data from a xml file (several tables inside) into sql tables.

- In the xml source, I choose the xml and xsd files and I see all tables perfectly.

- Drag the xml source output to Sql Destination input, choose one table to import and create the sql table

- I execute the task and it concludes ok

- In Execution Results window appears as warnings as fields of each one other tables (giving information about this fields are not used at the process and it'll be better removed it in order to increase performance), no errors and success task. The problem is that no one data is imported into sql destination (wrote 0 rows).

Process window:

Information: 0x4004300A at OPP, DTS.Pipeline: Validation phase is beginning.

Warning: 0x80047076 at OPP, DTS.Pipeline: The output column "field_1" (66374) on output "TABLA1" (50424) and component "XML Source" (27281) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

Information: 0x40043006 at OPP, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at OPP, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at OPP, DTS.Pipeline: Execute phase is beginning.

Information: 0x40043008 at OPP, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at OPP, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at OPP, DTS.Pipeline: "component "SQL Server Destination" (20427)" wrote 0 rows.

SSIS package "package.dtsx" finished: Success.

The program '[408] package.dtsx: DTS' has exited with code 0 (0x0).

If I import data of xml file from access, I haven't any problem. All tables are imported

I don't know what can be. Thanks a lot.

Gema

View 4 Replies View Related

Can I Get The Source To The Import Wizard?

Mar 5, 2007

Or is someone reading this a Wizard of Wizards. I need a 2nd flavor of import wizard. The one that I would clone out of the existing one would:
Use ASCII not UnicodeUse the copy column control not the Data ConversionThe size of all fields will be 255 not 50.The data type will be varchar.In my cloned wizard, the goal will be to just get the data loaded.
All other goals will be addressed by the user after the data is in the database.

Do I hear a volunteer?

Thanks,
IanO

View 2 Replies View Related

XML Source Import Problems - Need Help!!!

Oct 29, 2007

Hi all,

i've setup a SSIS project to import a fairly large XML file with multiple elements into their relevant tables with a few conversions along the way. Now, it all imports fine with no errors BUT, a few of the fields are not converting as i'd like. For example, i have a number of fields where SSIS believes them to be an Int value when actually i want them to be varchars (they have leading 0's on some). So, i've set the xsd to pass them in as "string" and convert it to the relevant length to stop any truncation errors. Some of the fields i'm converting are doing it as i ask but others are still dropping the leading 0's and i can't understand why as they're setup in the same way!!!

sample scripts:


<xs:element minOccurs="0" maxOccurs="unbounded" name="test">
<xs:complexType>
<xsequence>
some data eliminated to reduce space.................
<xs:element minOccurs="0" name="ACTIONCODE" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="RSPCODE" type="xstring" />
<xs:element minOccurs="0" name="TVR" type="xstring" />
</xsequence>
</xs:complexType>
</xs:element>



<test>

<ACTIONCODE>0</ACTIONCODE>

<RSPCODE>00123546</RSPCODE>

<TVR>0000000000</TVR>

</test>

Now, the TVR field imports correctly as a character field but the rspcode does not for some reason - it just drops the leading 0's!!!! There are a number of others that are doing the same but the TVR is fine (

I've also just noticed that the dataflow properties states the problem fields' Metadata datatype as being DT_UI8 etc as opposed to a string as stipulated in the xsd file!!!! This is obviously the issue but why is it picking it up as this type as opposed to what i've set it in the xsd???????????????

any ideas guys???

Cheers,
Chris

View 1 Replies View Related

Looping Through Excel Columns (256 Columns)

Sep 19, 2007

Hello All,

I have a problem and i wish i can get the answers or advices to solve it.

i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.


Any help will be alot appreciated.

View 7 Replies View Related

Import Wizard Using A PosgreSQL Source

Jan 6, 2006

I am trying to use the Import/Export Wizard to import all the tables and table data from a PostgreSQL database into dynamically created tables in my SQL 2005 database.  I have done this many times in SQL 2000 by selecting the PostgreSQL data source from the drop-down list in the wizard.  However, in SQL 2005 this data source is not available.

Yes, I did make sure the driver is installed, and I even created a saved Data Source and a linked server to the PostgreSQL database just to make sure there were no fundamental problems.  I also tried using the .NET odbc driver, but it does not give me the very important "Copy data from one or more tables or views" option... which is essential since I am dealing with hundreds of tables.

Any help is appreciated.

View 3 Replies View Related

Import From An ODBC Data Source Into SQL Server

Feb 7, 2007

Hi,

I am trying to import tables from an ODBC data source into an SQL Server 2005 database. I presume that one way to achieve this is to create an Integration Services package, via Business Intelligence Studio (I already used DTS in SQL Server 2000, but not Integration Services) ?

Or is there a simpler way ? For instance, is there an import wizard that woult include an ODBC Data source ?

Thanks i advance.

View 1 Replies View Related

Attempting To Import XML Source From Crystal Reports XI Causes Error

Feb 19, 2008



TITLE: Microsoft Visual Studio
------------------------------

Error at Data Flow Task [XML Source [55]]: There was an error setting up the mapping. The root element of a W3C XML Schema should be <schema> and its namespace should be 'http://www.w3.org/2001/XMLSchema'.



------------------------------
ADDITIONAL INFORMATION:

Pipeline component has returned HRESULT error code 0xC02090CF from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------


The error pasted above arose when I attempted to create a XML source based on a Crystal Reports XI report exported as XML. Is there any special modification to make to the XML exported from Crystal to allow SSIS to read it properly?

Thanks for your help,
Sid

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved