Creating sql server table from dts

I am trying to design a SSIS DTS package that imports an Excel named range (acts as a table) into a SQL Server 2005 database table. The direct import (using the import/export wizard) works fine, but I need to use a DTS package with a data transformation step. The DTS fails to create/replace/or update the SQL Server table. Suggestions?

Which is it? SSIS or DTS?

Can you clarify what you're trying to do?

How are you attempting to create/replace/update the table?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Kirk, Thank you for the response. Your book will arrive tomorrow and I expect it to answer my many questions. I am trying to use the SQL Server 2005 SSIS designer to produce a package (SSIS or DTS?) to copy a named range (proxy for an Excel 'table') from a network drive; convert the data types to what I want on the SQL Server 2005 server; delete the database table if it already exists and create a new table; and write the new data to the table. The connection to the Excel workbook named range and the data conversion step work fine. I get the following error in the destination step: [SQL Server Destination [98]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security." I have no idea what this means!

I am a 'user' rather than an 'IT pro' who is trying to build a small 'data-mart' for better management of data used for analysis, forecasting and strategic planning. A SSIS package appears to be the best means for accomplishing this task every month and many other data upload packages will be produced as soon as I understand the process.

Thank you for your help.

|||Kirk Haselden's book on Integration Services is very helpful. I highly recommend it!

Creating sql server table from dts

I am trying to design a SSIS DTS package that imports an Excel named range (acts as a table) into a SQL Server 2005 database table. The direct import (using the import/export wizard) works fine, but I need to use a DTS package with a data transformation step. The DTS fails to create/replace/or update the SQL Server table. Suggestions?

Which is it? SSIS or DTS?

Can you clarify what you're trying to do?

How are you attempting to create/replace/update the table?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Kirk, Thank you for the response. Your book will arrive tomorrow and I expect it to answer my many questions. I am trying to use the SQL Server 2005 SSIS designer to produce a package (SSIS or DTS?) to copy a named range (proxy for an Excel 'table') from a network drive; convert the data types to what I want on the SQL Server 2005 server; delete the database table if it already exists and create a new table; and write the new data to the table. The connection to the Excel workbook named range and the data conversion step work fine. I get the following error in the destination step: [SQL Server Destination [98]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security." I have no idea what this means!

I am a 'user' rather than an 'IT pro' who is trying to build a small 'data-mart' for better management of data used for analysis, forecasting and strategic planning. A SSIS package appears to be the best means for accomplishing this task every month and many other data upload packages will be produced as soon as I understand the process.

Thank you for your help.

|||Kirk Haselden's book on Integration Services is very helpful. I highly recommend it!

Creating Linked Server issues

I have a Merge Replication Setup. With 3 computers. One computer acts has a central publisher. The 2nd one is a subscriber, and republishes its data to the 3rd computer. Every thing is being done using Windows Authentication method. the 1st PC has SQL SERVER 2000 (Enterprise Manager, the works). The other two are SQL Server MSDE (Rel. A). My process is up and running and works, but heres my issue.

I need to script the replication installation process out., For future installations on machines that will be located in remote parts of the world. The will reside on a secure DSL network.

Anyone familiar with Scripting Merge Replication knows that once you've run certain scripts/procedures on your subscriber, you have to run a final procedure on the publisher to activate or initiate the subscriber as a valid one, and the you can start the Merge Agent on the subscriber, and voila! you're good. The procdure you run is called sp_addmergesubscription . It goes something like this :

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

My intention was to embed this system in a stored proc on the publisher and then call it remotely from the subscriber, before starting the Agent. IT FAILS!!! This is my proc:

Create Procedure RunSp_AM_subscription(@.servername Varchar(30), @.IAdbname Varchar(20), @.Attachedbname Varchar(20))
AS

DECLARE @.publicationname Varchar(30)
SET @.publicationname = 'REPUBLISH-'+@.IAdbname+''

BEGIN
exec sp_addmergesubscription @.publication = @.publicationname, @.subscriber = @.servername, @.subscriber_db = @.Attachedbname, @.subscription_type = N'pull', @.subscriber_type = N'global', @.subscription_priority = 56.250000, @.sync_type = N'automatic'
END

When I call it locally ot works fine, but not otherwise......... :eek:

When I try to create a linked server SQL Server seems to think that one already exists (because Replication has already created the said server as a remote server). I think the fact that I am not using SQL Authentication might be a problem too.

Does anyone have any ideas? I mean seriously, any suggestions on what to do. This is qute Urgent...........

Thanks.

'Walealso how would I even call the proc assuming I'm able to create the linked server.