ms sql dts package

I created a DTS package, which has a vbscript file to create a new directory, when i run it directly from the command line “DOS” C:\>dtsrun /Sxxxxxxxx /u!xxxx /pxxxxxx /NZOOT_NDM_COPY_and_ZIP_SCORECARD

, I get:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_3
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_3
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSActiveScriptTask_2
DTSRun OnError: DTSStep_DTSActiveScriptTask_2, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Path not found
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Path not found
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2
Error: -2147220440 (80040428); Provider Error: 0 (0)
Error string: Package failed because Step 'DTSStep_DTSActiveScriptTask_2' failed.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 700

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim fso,f1,f8,s,fldr,drv,fc,f
Set fso = CreateObject("Scripting.FileSystemObject")
f8 = DTSGlobalVariables("DATA_PATH").Value
' CREATE TODAYS FOLDER
if fso.FolderExists(f8&"\ZOOT_"&FormatDateTime(now,VBSHORTDATE)) then = LINE 12
'msgbox "directory Exists"
else
fso.CreateFolder (f8&"\ZOOT_"&FormatDateTime(now,VBSHORTDATE))
end if
' LOOP Through Files
'Change to a variable
Set f = fso.GetFolder(f8)
Set fc = f.Files
For Each f1 in fc
if cdate(left(f1.DateLastModified,10)) = cdate(datevalue(NOW)) then
f1.move (f8& "\ZOOT_"&FormatDateTime(now,VBSHORTDATE) &"\"&f1.name)
end if
' msgbox f1.name
Next
Main = DTSTaskExecResult_Success
End FunctionPlease verify the following:

Have you created a DTS from you EM Client or directly on the server ?

If this is the scenario then it gives you error. Instead of specifying the path like "c:\foldername\" try to give the UNC e.g \\servername\foldername"

May be it will help you

Thanks|||And ensure login used to execute the package has required privilege to complete the task or if you've scheduled same for the SQLAgent service account.

MS SQL 2000 full export (not backup)

Hi Everybody,

I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.

Many thanks.You should be able to use DTS to export the entire database at one time. Just right click on the database (in EM), click on all tasks and select export data.

Specify your source server/database, your target server/database and then select either copy tables and views or copy objects. Copy objects is more useful when you are trying to recreate the database structure. Copy data just moves over any tables.

Regards,

hmscott|||Thanks for your reply scott. my export is just a part of the work, because, i have to drop the entire instance and recreate it including 'master' database due to collation name change. So, i cannot export it to another database under this instance or to a different instance (due to space restriction). Hence my initial interest is to go with a flat (text) file. Is it still possible ?

Thanks|||Or...

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
@.dbName sysname
, @.fp varchar(255)
, @.User varchar(255)
, @.Pwd varchar(255)
AS
/*
EXEC isp_bcp_out_database
'Northwind'
, 'd:\Data\Northwind\'
, 'sa'
, ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
SELECT -- 'EXEC Master..xp_cmdshell ' +
-- '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
+ 'out ' + @.fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat '
+ '-S'+@.@.SERVERNAME+' -U'+@.User+' -P'+@.Pwd+' '
+ '-f'+@.fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
+ ' > ' + @.fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
-- + ', no_output' AS CMD
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @.CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @.CMD

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.CMD
SELECT @.CMD = 'ECHO ' + @.CMD + ' > ' + @.fp + '\bcpout.bat'
EXEC master..xp_cmdshell @.CMD
SELECT @.CMD = @.fp + '\bcpout.bat'
SELECT @.CMD
insert a (s)
exec master..xp_cmdshell @.cmd

FETCH NEXT FROM bcpout INTO @.CMD
END

CLOSE bcpout
DEALLOCATE bcpout

select id, ouputtmp = s from a

SET NOCOUNT OFF
GO|||Yurk. That's the second post I've seen today from someone needing to change the collation on their server.

I think for this you will sincerely want a second database server available as the target for your export. A developer license is cheap ($50?). Put it on a handy box and then away you go.

Short of that, you might be able to work out some combination of backup, re-install, restore (to different DB name), script off db, change collation specified in script, run script to create new DB and then use DTS to copy the data from the restored DB to the new DB.

Am I making sense? It IS getting close to 5:00 here. At 5:00, the value of my responses declines by at least 75%.

Regards,

hmscott|||Or just do what Brett (BCP King) Kaiser suggests.

Brett, is it any coincidence that your initials BK might stand for 'BCP King'?

regards,

hmscott|||Got a chuckle out of that one...

You can point that code at ANY database...

I think you'll need a DELETE in there for table a...I originally had it as a temp table (still want to delete a temp, so that's no excuse...just poor coding habit)

Oh and the table is to log the events for each bcp...

Let us know if it works out...just make sure you got plent of drive space...

I wouldn't do this over a network either...

just copy the files when they're done...|||Many Thanks for both of you (Scott and Brett).
I am browsing Brett's script. I am sure that, it will be a big help to get the export work done successfully. Hope I will get my next 15 instances converted from Latin1_General_CI_AS to SQL_Latin1_General_CI_AS ASAP. Thank you very much.|||15 Instances! On 1 box?

Wow

:eek:

MS SQL 2000 DTS, how to create connection with Applix TM1 OLE DB MD Provider?

Hi All,

I am trying to connect from MS Analysis Services 2000 to Applix TM1.
Applix has got Applix OLE DB MD Prodiver.It is installed on my machine.
I create a datasource in MSAS.
In Data Link I can see the this Applix OLE DD driver and I choose it. Then I can see Applix login and password screen. Then I logged in. When I test my connection, the connection is succeed.
But when I create a cube from data source,it gets an error message as below;

Connection teamnb-cem data source failed.
Object or provider is not capable of performing requested operation
Do you want to retry_?

How can I fix it?

BEst Regards

Cem DAGLI

AS2000 has list of supported providers, and TM1 is not one of them. There are many requirements for OLEDB provider to work properly with AS2000, so I am not optimistic you will succeed with this kind of direct connection. I suggest exporting data from TM1 cube into SQL Server first, and then processing AS2000 cube off SQL Server.|||

Dear Mosha,

I am using Applix OLE DB MD Provider. Not Microsoft providers.
When you install Applix , you have Applix OLE DB MD provider. I connected from Excel to Applix TM1.
But for AS2000 not.

Does AS2005 support Applix OLE DB MD Provider? If I try it from MSAS 2005 ,can I connect it?

Best Regards
Cem dAGLI

|||So, what exactly do you mean when you say "Connect AS2000 to Applix". The only way AS2000 "connects" to external databases is for processing or ROLAP, and there is a list of supported providers (SQL Server, Oracle etc) - Applix is not on that list.|||

Dear Mosha,

I mean connect from MSAS 2000 to Applix TM1 OLAP Server.

There is a Applix OLE DB MD Provider in Data Link screen in MSAS 2000. The Driver is provided by Applix.
I also connected from MS Excel to Applix OLAP server with this driver.
If you send me your e-mail , I will send all printed screen in MSAS 2000.

Best Regards

Cem DAGLI

|||

Cem

Unfortunately, you don't make yourself clear - you just repeated the information that you already provided earlier. The only way the statement "connect MSAS 2000 to Applix TM1 OLAP Server" can be interpreted is that you try to process MSAS cube out of the TM1 cube, and use Applix OLEDB provider for that. As I explained above - this is not supported scenario. If you have some clarifying screenshots - please attach them to this post.

|||

Hi Mosha,

I need to extract some data from Applix TM1 cubes into MS SQL Server. Here are the steps i tried to create a connection in MS SQL 2000 DTS package using Applix TM1 OLE DB MD Provider (v8.4.2).

1) Create a new DTS package
2) Add Connection Properties. Applix TM1 OLE DB MD Provider is not listed in the 'Data Source' list. So i choose Microsoft OLE DB Provider for OLAP Services 8.0

3) Click Properties...

4) On Provider tab, choose Applix TM1 OLE DB MD Provider.

5) On Connection tab, specify the TM1 server location & instance. Test Connection is successful.

6) Click OK to complete the configuration.

When i edit the connection properties again, the provider reverts to Micosoft OLMicrosoft OLE DB Provider for OLAP Services 8.0. It doesn't seem to save the configuration done above.

Could you please shed some lights on how to get this to work? Thanks a lot.