Converted Our DTS 2000 Packages To Sql Server 2005 SSIS

Nov 7, 2007

We are converted our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:



'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim oPkg

SET oPkg = DTSGlobalVariables.Parent

SET stpBeginJob = oPkg.Steps("DTSStep_DTSActiveScriptTask_1")

SET stpFinishedJob = oPkg.Steps("DTSStep_DTSActiveScriptTask_3")

IF DTSGlobalVariables("Count").Value > 0 THEN



stpFinishedJob.DisableStep = True

stpBeginJob.ExecutionStatus = DTSStepExecStat_Waiting

ELSE

stpFinishedJob.DisableStep = False

stpFinishedJob.ExecutionStatus = DTSStepExecStat_Waiting

END IF



Main = DTSTaskExecResult_Success

End Function


Error I get Is:

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x000A39BC.

Thank You

View 1 Replies


ADVERTISEMENT

Migrating Scheduled SQL 2000 DTS Packages To SSIS 2005

Feb 13, 2008

Below is a migration plan that I've compiled to migrate SQL 2000 DTS packages to SSIS 2005. Once these DTS packages have been migrated i will need to create a job and schedule them in SQL 2005.

I would appreciate and feedback or questions on this migration plan.

Migration DTS 2000 packages to SSIS 2005:

1. Will need to save the current production DTS package as structure storage file. We do not have a UDL file. We set the data connections within each DTS package.
2. Go to Sql 2005 - ManagementLegacyData Transformation Services - right-click and open previous saved structure storage file.
3. Modify the DTS data creditentials to reflect the SQL 2005 connection data. Modify any SQL 2000 MAPI settings to utilize SQL 2005 new database mail. Save the package on SQL 2005.
4. After the modified DTS package has been updated and saved on SQL 2005, save this file as a structure storage file.
5. go to BIDS. Create a new SSIS project. Right-click on SSIS packages and select Migrate DTS 2000 package. This will migrate over the DTS 2000 package with the updated SQL 2005 data creditentails.
6. click on the package properties - protectionlevel and change it to dontsavesensitive.
7. right-click and select package configurations..., select to store data creditentials in xml format.
8. right-click on execute DTS 2000 package task, select Edit... and click on Load DTS2000 package internally. This will embed this task into the new SSIS package. Test the package. Continue if successful.
9. Use SSIS deployment functionality to move the package over to SQL 2005 Integration Services. Right-click on package and select Run Package, if successful, create a job and schedule it to run on SQL 2005 Agent.
10. When creating the Job under SQL Agent, change the Owner: of the job to reflect the owner of the new SSIS package. Schedule the job.


I've just coming up to speed on SSIS 2005. Therefore, this is what i've been able to piece together up to this point and I'm looking for some industry advice/feedback on whether or not this is a good migration plan. I need to provide a migration plan to management by 2/18. Thanks

View 1 Replies View Related

Using SSIS Packages To Process SQL Server 2000 OLAP Objects

Nov 16, 2006

Can a SSIS server, i.e. staging server be used to create packages that update SQL Server 2000 Analysis services objects on another server running SQL server 2000 OLAP? 

It appears that the OLAP connection manager ion SSIS supports only connections (and thus updates) to 2005 OLAP objects.  I work for a company that has a huge investment in a 3rd party DW that uses Analysis Services 2000.  the DW tool vendor will not support an upgrade to SSAS 2005.  We wish to extend the DW from other data sources.  My thought was to use a staging server with SSIS, used solely as the ETL tool for all new development (thus no more DTS development), and to update the sql server 2000 operational data store on another box. 

I can find no documentation on how to process sql server 2000 analysis services objects from within an SSIS package.   Any ideas?

 

 

View 4 Replies View Related

Running SSIS Packages In MS Server 2005

Jan 31, 2008

Hi
Just a brief question,
is there a way of displaying a success or not success message when running (testing) an SSIS package on SQL 2005 ?

Because i have to go through the steps to see if there was an error or not and for large SSIS packages this is a pain.

With DTS 2000 after it ran you got a message saying successfull or not, is there a way to activate this in SQL 2005 ?

I suppose i could just run the job ... but I prefer the other way as sometimes a job fail is a permissions problem, not a SQL/datapump task problem...

View 1 Replies View Related

MS SQL Server 2005 SP2 Problems With SSIS Packages

Mar 8, 2007

I installed SP2 to one of our servers to see what we'd need to do for our Production systems. The server in question is used for a log shipped copy of production, SSRS, SSIS and general duty stuff. I got errors in two areas during the installation -- 1 dealing with SSNS (don't use it yet on that server) and the second being the Client product.

After a reboot I went into a Business Studio solution I've been working on (and using) for several months. None of my packages are working--each having the following problem:

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'.
This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}'
failed due to the following error: Library not registered. (Exception from HRESULT: 0x8002801D (TYPE_E_LIBNOTREGISTERED))..

It seems to be in the designer as it is unable to display the the graphical version but it does let me switch to the code view.

What is the easiest way to troubleshoot and/or fix this?

View 6 Replies View Related

Scheduling The Legacy DTS 2000 Packages In Sql Server 2005?

Sep 12, 2007



Hi Experts

I am trying to schedule a legacy DTS 2000 package in 64 bit Sql Server 2005.

I cannot use the DTSRun tool on the Command prompt of the Sql 2005. What about dtexec ?Can i run Sql Server 2000 DTS packages from the Command Prompt.

Is DTS 2000 the only way to do that?

Any Advise is greatly appreciated

Vic.

View 5 Replies View Related

Visual Studio 2008 And SQL Server 2005 SSIS Packages

Mar 3, 2008

My home machine has sql 2005 and Visual Studio 2005 installed. I uninstalled VS 2005 (RTM) and installed VS 2008 (RTM) in its place. I then found that the SSIS templates were missing. Lesson: don't uninstall VS 2005 if you want to use SSIS 2005!



1. Is there any "workaround" to this issue?



2. (If the answer to 1 is "No") I will install VS 2008 side-by-side with VS 2005 on the same box. When the SSIS issue is finally resolved, can I uninstall VS 2005 without doing damage to the VS 2008 install?



TIA,



Barkingdog




View 17 Replies View Related

Integration Services :: Remotely Execute Packages On SSIS Server - Packages Are Deployed In File System

Apr 22, 2015

We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.

SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.

We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.

One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.

View 4 Replies View Related

Dts 2000 Packages With SSIS

Sep 22, 2006

I need help,

I've got 51 pretty complex Dts packages that are running on Sql 2000. I'm
trying to make them run on Sql 2005 without at first migrating them with the
migration wizard, since i know that many of them cannot be migrated.

I'm using the "Execute DTS 2000 Package Task Editor" to make them run in an
SSIS package, some of them are running fine, but many of them, especially
those with a "Data Driven Query Task" are returning this error:

Error: System.Runtime.InteropServices.COMException (0x80040427): Execution
was canceled by user. at DTS.PackageClass.Execute() at
Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()

After many research on the web, i've reinstalled the Sql 2000 tools on the
Sql 2005 server, but i'm still receiving the same error. Also, i've tried to
repair the "Microsoft Sql Server 2005 backward compatibility" in the
Add/remove program of the Sql 2005 server without much success.

For your info, i've installed the hotfix Build 2153 on both the server and
the client and i'm still having the same problem......

Any idea?

Thanks.

View 2 Replies View Related

Execute A Stored Procedure Which Calls SSIS Packages Using A Proxy Account For Non Sysadmins In Sql Server 2005

Sep 18, 2007



Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.

Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message

Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.


....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.

so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....

So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.

If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....

Thanks
Raja.V

View 2 Replies View Related

Sql 2000 DTS Packages On Sql 2005

May 24, 2007

Hi Friends

When i open the DTS packages in the Management Studio for editing the DTS packages

I get an error prompt asking me for Installing the Sql Server 2000 Designer Components. I already have the Sql Server 2000 Client tools installed in my machine. Should i still install the designer components...Wont there be a crash in the Client tools side???

cheers

Vic

http://vicdba.blogspot.com

View 3 Replies View Related

SSIS Too Strict On Conversion With No Option To Overwrite, Forcing Me To Use DTS 2000 Packages

Nov 1, 2007

This is more of a philosophical post, but feedbacks are welcome!

I am working at migrating SQL 2000 DTS packages that pulls data from MAS90 via ODBC connections.
At first, I REALLY tried to learn SSIS and I hated it at first, with all the new things one has to do to get a simple import to work. After a while, I begin to appreciate some of the new design and the more tiered approach. Indeed, I tried to use SSIS to import the tables and even learned how to overcome the Unicode/non-Unicode conversion errors by using the Import Wizard to do the grunt work.

But today I came across a show stopper: My imports are failing because the source lied about its metadata type and I am getting a "Value too large for output column" error. I tried to recreate the Task to no avail. I searched on the web and there are very few posts regarding to this and unfortunately I don't have a way to tweak my ODBC connection properties for MAS90 to some how "fool" SSIS. I finally give up and migrate the DTS 2000 package instead.

I am not too happy about this solution because I know that more likely or not Microsoft will discontinue support for such legacy approach and then it is more work down the road. I REALLY wanted to do it right, to rebuild it natively in SSIS but why does SSIS have to make things so hard by enforcing the type checks so tightly? Is it so bad to allow users who know the data better to by pass the validations? We are not working in a perfect Comp Sci 101 world where every thing is scrubbed clean, we work in a world of bad, old, malformed data.

If there is a way for me to overcome that "value too large" error, I am all ears.
Thank you for reading.

View 1 Replies View Related

MS SQL 2005 SP2 X64 &&amp; SSIS Packages

Aug 22, 2007

On my local desktop, I can create and run ssis packages, when I try to do the same thing on server I get he following error right clicking on running packages or stored packages.

failed to retrieve data for this request. Library not Registered. (exception from HRESULT: 0x8002801D

there are 2 instances of SQL on this server. Both are named with one being use by SQLExpress and the other by SQL2005 Std

View 16 Replies View Related

Move DTS Packages From SQL 2000 To SQL 2005

Jun 12, 2006

We have purchased a new Development SQL Server that is running SQL Server 2005. Our existing Development SQL Server is running SQL 2000.

How can I move the DTS packages from the SQL 2000 server to the new SQL 2005 server? I will be upgrading them to SSIS at some point, but don't have the time now.

thanks

View 1 Replies View Related

MIgrating DTS Packages Of SQL 2000 To SQL 2005

Jan 5, 2007

Hello All,

I am working on a migration project of my database(lets name it DB1), from SQL 2000 to SQL 2005. I have some DTS packages in my SQL 2000 database which also needs to be migrated to SSIS. These DTS packages currently interacts with other database(lets name it DB2) which is also in SQL 2000 and which does the data transmission. The real issue is that the other database(DB2) also has some DTS packages which also communicates with DB1 (the db to be upgraded) and so when i migrate my DB1 to SQL 2005, i will have to change my DB2 packages also (although the change will be minor). Now considering that in future I might also migrate my DB2 to SQL 2005 I wanted to know what will be the right approach to follow. One is to modify the DTS packages of DB2 to accmodate the change in connection and the other is to migrate the DTS packages of DB2 also to SSIS.

Can anyone help me with this one.

Thanks in Advance

Mitesh

View 5 Replies View Related

Scheduling SQL 2005 SSIS Packages

May 9, 2007

I have created and run SSIS packages from my workstation that are successful. When I Build and then Deploy the same package to the server and schedule it, it fails. I am using SQL authentication connecting from a SQL 2000 database to a SQL 2005 database via OLE DB connections.

The following is the exported result of the package...



Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by User JDEREP. The last step to run was step 1 (step1).,00:00:01,0,0,,,,0

05/09/2007 15:46:05,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:46:06.38 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:46:06.38 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:46:05 PM Finished: 3:46:06 PM Elapsed: 0.75 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:30:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:30:01.15 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:30:01.15 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:30:00 PM Finished: 3:30:01 PM Elapsed: 0.734 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:20:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:20:01.55 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:20:01.55 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:20:00 PM Finished: 3:20:01 PM Elapsed: 0.703 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,0,AMSBINT1,AMS_EnterpriseWarehouse,(Job outcome),,The job failed. The Job was invoked by Schedule 29 (AMS_EnterpriseWarehouse). The last step to run was step 1 (step1).,00:00:01,0,0,,,,0
05/09/2007 15:18:00,AMS_EnterpriseWarehouse,Error,1,AMSBINT1,AMS_EnterpriseWarehouse,step1,,Executed as user: AMSOILvector. Error: 2007-05-09 15:18:01.53 Code: 0xC0047017 Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: component "F0005" (1) failed validation and returned error code 0xC020801C. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC004700C Source: JDE Production to AMS BI 7332 DTS.Pipeline Description: One or more component failed validation. End Error Error: 2007-05-09 15:18:01.53 Code: 0xC0024107 Source: JDE Production to AMS BI 7332 Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 3:18:00 PM Finished: 3:18:01 PM Elapsed: 0.766 seconds. The package execution failed. The step failed.,00:00:01,0,0,,,,0

Any help would be greatly appreciated...



Jim



Additionally, when I run this from the command line, I get the following



DTSER_FAILURE (1)



Jim

View 7 Replies View Related

Moving Sql 2000 DTS Packages To Sql 2005 Standard Edition

Feb 23, 2006

I have been tasked with upgrading around 150 SQL Server 2000 DTS packages to SSIS in SQL Server 2005 standard edition. I made a backup of the 2000 database upon which the DTS packages operate and restored it to the SQL 2005 server. So far, so good. I have the database in place. Now I need to get the DTS packages themselves into the SLQ 2005 server. I think I need to check my install and make sure that I have the SQL Server 2000 DTS services installed on the SQL 2005 server. I can do that.

However, I wonder what would be the most effective way to physically get the packages from the SQL 2000 server to the SQL 2005 server. Should I use structured storage files? If so, how do I go about opening them in SQL 2005 in order to save them to SQL server 2005?

I should mention that these packages make heavy use of ActiveX scripting so I am looking at rewriting them from scratch to be SSIS packages. I just need the packages on the SQL Server 2005 box so I can make sure I am creating exactly the same functionality in 2005 as existed in SQL server 2000. Each DTS 2000 individual package tends to be fairly simple and I think I can greatly improve the process by consolidating them.

Thanks in advance for any advice.

S. Wells

View 3 Replies View Related

2000 DTS Packages Corrupted By 2005 Client Tools?

Jan 12, 2006

Hi,

I have many sql 2000 DTS packages that I support from my development workstation  running v2000 sp4. Packages are altered on the development machine and then go through a normal release mechanisms to production via testing servers etc.

I have recently installed the client tools for SQL Server 2005 on my desktop to evaluate the product.  The 2005 DB instance is running on a seperate server.

So, I have dev edition of sql 2000 and 2005 client tools (including BI Dev studio etc) on my workstation.

I have recently had to make changes to a 2000 DTS package and used my 2000 enterprise manager to do so. No Problem- saved and tested fine on my workstation. 

But when I try and release it to another server, or open the package using enterprise manager from another machine that does not have sql 2005 installed - I get an error message 'Unspecified error'.  This I've seen before when trying to open packages created in v2000 , using v7 or where the service packs are different between machines.

Digging around my workstation and comparing some of the DLLs I know to be required to distribute DTS  packages (from RDIST.txt) it seems that some of the SQL 2000 dll files have been updated by my 2005 installation. 

E.g

DTSFFILE.DLL on my machine is 2000.85.1054.0 whilst on any 'clean' 2000 machine is at version v2000.80.760.0

Surely it cant be right that SQL 2005 has newer versions of components for SQL 2000 than is available with the latest SP for the actual product! Especially considering that the installation of 2005 does not even allow you to edit 2000 DTS packages through the management studio without a 'special' download' of the feature pack,(whihc by the way does not work very well either)

So am I to conclude that you can not run side by side installations of SQL 2000 and 2005 on a single machine and expect 2000 to run as it did previously

View 11 Replies View Related

SSIS 2005 Dependency For VS2005 Setup With Packages

Dec 14, 2006

I need to deploy some SSIS packages onto a remote Integration Server machine. Hence
I wrote a VS 2005 Setup with Custom Action (C# Installer class) which uses the
SSIS DTSX Dlls to deploy the Packages onto an Integration Server. I am shipping
all the necessary DLLs in the MSI itself, so that the MSI can run on any client
machine having just .NET 2.0 Framework installed.



When I run the Setup on a Machine having Integration Server, the Setup works
fine and the Packages are deployed.



However, if I try to run the MSI on a machine which just has the .NET 2.0
Framework installed, I get the following error.



SqlServer.DTS.Runtime.Application could not be created. Retrieving the COM
class factory for component with CLSID
{E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error.
80040154



I am not sure whether any of the DLLS are COM components. I believe all are
Managed DLLs.I believe SSIS 2005 looks like a pre-requisite but I don't need that.


Please help. I am posting this query here, as I believe ppl writing SSIS Packages may have faced this issue.

View 4 Replies View Related

Can I Access SQL Server 2000 Tables Via SSIS/SQL Server 2005

Apr 9, 2007

Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?

View 1 Replies View Related

Moving Data In Ssis From Sql Server 2000 To 2005 On Same Server

Feb 9, 2007

hi, does anyone know how I can make a connection to a 2000 db thru ssis?

View 8 Replies View Related

Migrate SQL Server 2000 DTS To SQL Server 2005 DTS Or SSIS

Apr 8, 2008



We have DTS packages in SQL Server 2000, which populates the data from SQL 2000 to Ms-Access tables.
We are moving our database from SQL Server 2000 to SQL Server 2005.

Why & How & What is the best approach to migrate DTS packages?

Migrate SQL Server 2000 DTS to SQL Server 2005 DTS Or SSIS

View 3 Replies View Related

SQL 2012 :: Running 2005 SSIS Packages That Read FoxPro Files?

Aug 26, 2013

We are in the process of upgrading to a new SQL 2012 server but we have many packages that load data from dbf files created with FoxPro into one of our databases. We have not converted the packages and run them with DTS but we get the following error:

Error: 2013-08-26 11:05:27.36
Code: 0xC0209303
Source: BenchmarkLoad Connection manager "OLEDB NPIONE.Investment.middleTierSQL"
Description: The requested OLE DB provider SQLNCLI.1 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

[Code] .....

I searched for OLEDB and ODBC drivers for SQL 2012 64 bit but cannot find any that is newer than the 2005 that we have. What can I do short of changing the source files to overcome this issue?

View 8 Replies View Related

The Value Could Not Be Converted Because Of A Potential Loss Of Data In SSIS Package

Mar 3, 2006

With my SSIS package, I want to import data from a flat file (TXT- delimited with ?) to a table in my database in sql server 2005. The problem is that I have a column of type datetime in my table. But as you know, the data in txt is string. First I created my package through importing data and using import/export wizard in management studio to my database and selecting flat file connection. There, I selected my txt file and column delimiter as ?, then suggested types for the columns. There it selects 8 byte signed integer type for the datetime column in my table. After these steps I create my package and execute it. But it does not put data in my table in the database. It gives the error of "The value could not be converted because of a potential loss of data" or "cast conversion failed" . I tried other types of date, timestamp, string but none of them was successful. What should I do to put data in my table from txts. Please can you help me urgently!!!

Thanks a lot

View 12 Replies View Related

Calling SSIS Packages From ASP.NET - Packages With File System Tasks End Abruptly

Jan 9, 2007

I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.

Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.

When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.

I've tried giving administrative permissions to the ASPNET worker process user to no avail.

I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.

I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.

Anybody got any idea on how to go about this?

View 33 Replies View Related

Keyboard Not Working (backspace And Arrow Keys) When Viewing 2000 DTS Packages From 2005 (backward Compatibility)

May 4, 2006

I know my problem is not directly associated w/ SSIS, so please forgive the post here. I figured this would be the "most" appropriate place to post this challenge.

We're attempting to edit a 2000 DTS package in Design mode from within the 2005 SQL Management Studio. To do this we downloaded/installed the following packages from the MS download site:

Microsoft SQL Server 2000 DTS Designer Components
Microsoft SQL Server 2005 Backward Compatibility Components

We're able to open the package in design mode from the Management Studio (Server > Management > Legacy > Data Transformation Services > DTS Package Name). But, when the DTS designer is open, the backspace and arrow keys won't respond in the Management Studio. They begin responding when we close the DTS designer. There are no error messages when we close the DTS designer.

Does anyone know of a fix or a workaround? Any help would be greatly appreciated.

Thanks, Mike

View 7 Replies View Related

Creating DTS Packages In SQL Server 2000 EM

Jan 16, 2001

I have a SQL Server 7.0 backend server but I am using SQL Server 2000 EM to create DTS packages. Can the DTS packages be opened in SQL Server 7.0? We are contemplating the install of SQL 2000 but we are worried that any new DTS packages created won't work when opened on a SQL Server 7.0 server or that the SQL 2000 DTS functionality is just not compatibile with SQL Server 7.0 and will cause problems.

Any ideas out there?

Val

View 2 Replies View Related

Executing SSIS Packages On An App Server

Feb 8, 2008

I have been trying to get a clear explanation on what I need to do in order to be able to execute SSIS packages which I have located on an app server. Is it that I need integration services installed on the app server itself and thats it? Or can someone tell me if there is any more to it, thanks folks!

View 1 Replies View Related

Executing SSIS Packages On A Non SQL Server PC

May 23, 2008

With DTS you could copy and register some DLLs onto a non-SQL Server PC and run Packages. Is this still possible with SSIS? I imagine that, at the very least, you would have to copy dtexec.exe and the *,dtsx files to the other computer.

View 9 Replies View Related

Placing Ssis Packages On Server

Apr 3, 2007

I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....



And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.



I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..



Please help me with all my questions

Thanks in advance..



View 5 Replies View Related

Batch Import Packages Into SSIS Server?

Apr 22, 2008

Is it possible to import packages in batch into MSDB (in lieu of right clicking and importing each one by one)? We have a lot of packages that are going through a lot of changes so a batch import would save a lot of time.

Thanks.

View 5 Replies View Related

SQL Server 2000 Meta Data Services Packages

Dec 5, 2006

Would someone help me how to Move existing Meta Data Services packages to SQL Server storage (in the msdb database) or to structured storage files before you upgrade from SQL Server 2000 to SQL Server 2005? This is a before action recommendation by Upgrade Advisor.

View 1 Replies View Related

How To Copy The Data From One Server To Another Server Using Sql 2000 DTS Packages

Nov 12, 2007

I am trying to copy/update a table which is in server2 based on a similar table which is in server 1. I can't use replication,
so I am thinking which are the best ways to do without affecting the performance as the source table is busy with inserts or updates. I am thinking of following options:
1. SQL server 2000 DTS packages: As I am trying to copy the data from the source table(server1) into a destination table which is a similar table in server2, if I use dts first I want to take the complete snapshot and then on I only want to copy the new transactions or updated transactions, please let me know how I can do this.
2. Does trigger affect the performance as the server1(source table) is a busy server.
Please let me know. Thanks.

View 12 Replies View Related







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