Import Data to SQL server from Excel spreadsheet

Hi all,

Firstly, i'm new to integration services and have only done a little with DTS jobs.

I'm trying to create an integration services project which will import data from an two worksheets in an Excel spreadsheet to two different tables in a database. I'm looking at only one table at present to make things a little more understandable.

One stipulation i have is that i need to be able to specify a variable value and insert that as an additional column in the database. I have and Excel source and a SQL destination both of which have been set up with there specific connection managers. I also have a variable which i add in using the derived column task.

When i try to debug this i am getting a few problems. I think these may be to do with the fact that although the worksheet in Excel has 20 rows (1st column shows these numbers) i only want those rows with data in them. If i preview the excel table it shows all the rows including those with null columns. Is there some sort of way that i can only get the rows that have data in the columns after the row number. I.e. can i select rows that do not have a second column value = to NULL.

I hope this makes sense and that someone can help me out with this problem.

All help is greatly appreciated.

Cheers,

Grant

P.S.

Apologies. I have this resolved now. I didn't see the option to use a SQL command as apposed to a table or view when setting up the Excel source.

I am still however getting the following errors which i'd appreciate some help on:

Error: 0xC0202009 at Data Flow Task, Excel Source [1]: An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC0208265 at Data Flow Task, Excel Source [1]: Failed to retrieve long data for column "Rework Entry Information (BE SPECIFIC)".
Error: 0xC020901C at Data Flow Task, Excel Source [1]: There was an error with output column "Rework Entry Information" (170) on output "Excel Source Output" (9). The column status returned was: "DBSTATUS_UNAVAILABLE".
Error: 0xC0209029 at Data Flow Task, Excel Source [1]: The "output column "Rework Entry Information" (170)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "Rework Entry Information" (170)" specifies failure on error. An error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Excel Source" (1) returned error code 0xC0209029. 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.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: 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.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Any help on this would be greatly appreciated.

GrantI'd also like to know how to go about specifying a variable as the datasource for my Excel connection. This is so that at runtime i can specify a number of different files to process.

Thank you,

Grant|||

You can use a ForEach loop container in your control flow to go through all files in a specific file system folder; then inside of that ForEach container add a dataflow task that does what you want. You may need to use an expression to change the connection string of your Excel Connection manager for every iteration (using the variable that has the collection value). I have never tried that before; this is just an idea.

good luck!

Rafael Salas

|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio. If you're using SQL 2000, try using the appropriate version of Enterprise Manager.|||I had a similar problem importing into a SQL Server 2000 database with SQL Managment Studio for SQL Server 2005. If you're using SQL Server 2000, try using the appropriate version of Enterprise Manager.

Import Data problems

I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
all of the databases, backup jobs, etc.. from another server running Windows
2000 server. When I use the Import Data feature to bring over a database I
get binding errors. Is there an easier way to bring over all of the databases
and settings from SQL2k?
These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/colu...rdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:C35F6CF3-4EFC-4CDB-8A9A-483132C8376E@.microsoft.com...
> I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
> all of the databases, backup jobs, etc.. from another server running
> Windows
> 2000 server. When I use the Import Data feature to bring over a database I
> get binding errors. Is there an easier way to bring over all of the
> databases
> and settings from SQL2k?
>
>

Import Data problems

I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
all of the databases, backup jobs, etc.. from another server running Windows
2000 server. When I use the Import Data feature to bring over a database I
get binding errors. Is there an easier way to bring over all of the database
s
and settings from SQL2k?These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:C35F6CF3-4EFC-4CDB-8A9A-483132C8376E@.microsoft.com...
> I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
> all of the databases, backup jobs, etc.. from another server running
> Windows
> 2000 server. When I use the Import Data feature to bring over a database I
> get binding errors. Is there an easier way to bring over all of the
> databases
> and settings from SQL2k?
>
>

Import Data problems

I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
all of the databases, backup jobs, etc.. from another server running Windows
2000 server. When I use the Import Data feature to bring over a database I
get binding errors. Is there an easier way to bring over all of the databases
and settings from SQL2k?These might help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
--
Andrew J. Kelly SQL MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:C35F6CF3-4EFC-4CDB-8A9A-483132C8376E@.microsoft.com...
> I've setup a new Win2003 server with SQL 2000 server. I'm trying to import
> all of the databases, backup jobs, etc.. from another server running
> Windows
> 2000 server. When I use the Import Data feature to bring over a database I
> get binding errors. Is there an easier way to bring over all of the
> databases
> and settings from SQL2k?
>
>