Can you assist me with converting the code below to VB ?
Function Main()
Dim objExcel
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:FTPOUTGOINGFTP_MarkOff.xls",,,,""
objExcel.Workbooks(1).SaveAs "C:FTPOUTGOINGFTPMarkoff.xls",,"password"
objExcel.Workbooks.Close
Set objExcel = Nothing
Main = DTSTaskExecResult_Success
End Function
I am re-writing old DTS packages(from sql 2005) to convert them to SSIS packages(sql 2014) and in one of the script task, the old activex script does not run.
The script is :
'************************
' Visual Basic ActiveX Script
'************************
Function Main()
mydate = now()
yrs = ""
mth = ""
mth = Month(mydate) - 1
yrs = Right(Year(mydate), 4)
If Month(mydate) = 1 Then mth = "12"
[code].....
Not sure how to proceed forward? I am quite new to package migration
I am re-writing old DTS packages(from sql 2005) to convert them to SSIS packages(sql 2014) and in one of the script task, the old activex script does not run.
The script is :
'************************
'Â Visual Basic ActiveX Script
'************************
Function Main()
   mydate = now()
   yrs = ""
   mth = ""
   mth = Month(mydate) - 1
[Code] ...
Not sure how to proceed forward? What is the SSIS counterpart of above script, step by step?
Hi all
I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS that connects to an AS400 to retrieve record count for integrity checking. Under SQL server 2000 this worked fine but now it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server on windows 2003. The odbc driver for the as400 has been installed also. The script is as follows:
dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")
Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%
'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"
set rs1 = cn.execute (insertstr1)
'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function
Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks
Chris
I migrated the DTS from 2000, and the migrated SSIS which includes ActiveX script is 11KB, takes 00:00.125 running. I rewrote the SSIS using some new features provided in BIDS, and the new package is 50KB, takes 00:00:6.016 running. Is that normal or maybe because of the efficiency of my code?
thanks.
In good old fashioned DTS there was the ability to perform custom transformations using activeX / vbscripty type language - does this still exist or are we stuck with the derived column editor?
View 3 Replies View RelatedI have a flatfile source to which different flatfiles will be passed as input,this is connected to an OLEDB destination which changes along with the sourcefile.
But when the new file is given as input, the OLEDB mappings are not getting refreshed.It is showing an error.
Actually this was implemented in DTS, and they have used an activex script for the transformation.
what shd I use in SSIS?
Please help me..
Sharmada
Within a SQL 2000 DTS Package I have an ActiveX Script that would go within my transform tasks and update the queries by concatenating a "Where" clause with a date from a database table. This way I could keep track of when the last time I updated the table so that I could only bring down the rows since the last run. How can this be done within SSIS? I've been looking and I'm getting confused. Any help would be greatly appreciated.
View 2 Replies View Related My ActiveX code wrote:
Function Main()
msgbox "Hello" & DTSTaskExecResult_Failure & DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
End Function
However - it displays "Hello10" - but SSIS will not throw an error - it executes successfully.
Any ideas?
Hi,
In my script 'm using
Set oPkg = DTSGlobalVariables.Parent
Set oBeginStep = oPkg.Steps("DTSStep_DTSExecuteSQLTask_1")
I guess becoz of using DTSGlobalVariables.Parent in activex,the script is not working.
Can anyone please suggest a solution to run it in SSIS.
Thanks in Advance
HI Experts,
I am having 2 severs (SQL2000 & SQL2005), there is already one DTS package in SQL 2000, now i have migrated DTS package to SSIS package. Now the problem is DTS package is having ActiveX script and it was executing perfectly in 2000 server but after migrated to 2005 server if i am executing the coverted SSIS package it is giving one error and the error is displaying here.
Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. SearsCDCC_Transfer (1).dtsx 0 0
and i am also displaying the ACtiveX code as well and here oma11pngrdb02 is 2000 server and sant01pngrdb is 2005 server.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Dim gsErrorFile
gsErrorFile = "\oma11pngrdb02App_Data_ReportingSearserrors2.txt"
Function Main()
Main = DTSTaskExecResult_Success
set objftp = CreateObject("ftpx.aspftp")
objftp.sServerName = "10.1.3.175"
objftp.sUserID = "COS.FTP"
objftp.sPassword = "SENDFILE"
if objftp.bConnect then
dim fname, lfname
fname = "$IOMH04.PRDFFTAP.ST462330"
lfname = "\oma11pngrdb02App_Data_ReportingSearscdccfile.txt"
If objFTP.bGetFile (fname, lfname ) then
Main = DTSTaskExecResult_Success
Else
logError("Error getting file: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
End If
Else
logError("Error connecting: " & objFTP.sErrorDesc)
Main = DTSTaskExecResult_Failure
end if
End Function
Function logError(sErrorMsg)
Dim objErrorFile, objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objErrorFile = objFSO.OpenTextFile(gsErrorFile, 2,True)
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.writeLine FormatDateTime(Now(),vbShortdate) & " " & FormatDateTime(Now(),vbShorttime)
objErrorFile.writeLine sErrorMsg
objErrorFile.writeLine "Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! Error!! "
objErrorFile.close
Set objErrorFile = Nothing
Set objFSO = Nothing
End Function
Can any one give me the solution as early as possible, and your help is soo Appreciable
Dear all,
I€™ve made a SSIS package which might take source columns from a plain text file and copy them to the Sql table.
A long time ago, when you did the process I did by dts and that stuff included a pump task which had ActiveX Script transform column with VbScript stuff inside so that, how do I for to do the same with SSIS??
I€™ve got a couple of tasks: Flat File Source and OleDb Source Destination but it€™s useless at all for that goal.
Does anyone have ever used or faced this hended?
Thanks for any input,
I have an ActiveX component in my SSIS package, and it is written in VB. Something is going wrong, and I'm not sure what. I works fine when run from Visual Studio, but when I move it to our server and try to run it from a job there, it fails. I'd like to know WHERE in the component it is failing, so I'd like to send output to something as it goes along, so I can see how far it is getting.
How do I send text to an output or log file?
I can't use MsgBox, because, of course, when it runs as a job, it is putting that message box up on the server, where there is no-one to respond to it, so it hangs. I'm in the process of converting it from a DTS to SSIS, and it does have several instances of MsgBox now. And it's locking up -- on one of them.
I have logging turned on for the job step, and it is writing to a dbo.sysdtslog90 table, but all it tells me is that it is starting that ActiveX script task.
What can I replace the MsgBox with, so that it outputs somewhere to a file? Is there a simple command, like WScript.Echo or Console.WriteLine (neither of which I can get to work)?
Thank you for any help you can provide.
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
I need to retrieve the Global Variables set in my package configuration file within an ActiveX Script Task within an SSIS package. In DTS, I could access the Global Variables to execute a SQLXMLBulkLoad for the following statement:
==========================================
Function Main()
Response.Expires=-1
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString =
"provider=SQLOLEDB.1;server=ABC123;database=MyDB;Trusted_Connection=Yes;"
objBL.KeepIdentity = False
objBL.CheckConstraints = False
objBL.Execute DTSGlobalVariables("gv_XSDSchemaFile").Value, DTSGlobalVariables("gv_XMLFullPath").Value
Main = DTSTaskExecResult_Success
set objBL=Nothing
End Function
=========================================
I have tried using the Script Task to write this in VB.NET, however the MSXML4.0 is not exposed within the limited object model of the Script Task Designer. I have written a Data Flow Object using the XML Source, however it requires quite a bit of effort to have the Data Flow Component parse the XML (with 10 hierarchical nodes), transform each and provide a SQL Server Destination. This works, however the XML Source Component requires a hardcoded reference to the XSD Schema file and does not allow for a Global Variable to used. (They do provide this functionality for the XML file source though).
My requirement is to allow for the Global Variable to be passed for the Schema file at runtime. The only way I can think of is to recreate what I was doing in DTS where I could simply pull in the XML and XSD Global Variables and execute the SQLXMLBulkLoad in VB Script.
Any ideas on how to write this in VBScript within the ActiveX Script Task in SSIS?...
Michael
I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
I have date in Flat file and it is in the string format,but now i want to convert it in to normal date format.I have tried doing this by SSIS but it is not working.
View 12 Replies View Related
what is the use of Data Conversion
please give me an example
I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.
What am i doing wrong ?
DTEXEC /DTS "File SystemArchive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:InetpubwwwrootoutputArchive'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"
Error I get
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds
I tried to setup a flat file data source that has code page 37 (EBCDIC)
Then I have a flat file destionation that is ASCII.
And inbetween I have tried several different data flow conversion tasks liked Data Conversion, and Derived Column. But I keep getting errors about different code pages.
I also tried to load the EBCDIC data into a SQL Server DB, and it complains about different code page.
Has anyone been able to do this with SSIS out of the box, without any extra components ?
Clarence
I have been trying to read a flat file which has a birthdate field. The field is string and the format is "011594". I have been trying to convert it to a date field in the following format "01/15/1994". Using the forum I was able to write the following expression but I am still keep getting error messages.
I also noticed few records are missing birthdate and few do not have the complete birthdate which means some birthdates are missing year and some are missing month. For example, some are completely missing and some are partially missing, like " " or "0312 ". Could this be the reason I was getting error message since the following expression does not include null and incomplete date of birth?
(DT_DBDATE)(SUBSTRING([DateofBirth] ,1,2) + "/" + SUBSTRING([DateofBirth] ,3,2) + "/" + SUBSTRING([DateofBirth] ,5,2))
Here are the error messages:
[Derived Column [1014]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
Hello,
I have a ssis package that I created in the BIDS and run through a job.
the package was running very good all the time, till yesterday, when I changed the
default language of my server to hebrew (its an hebrew website).
now I get this error:
Code SnippetError: 0xC002F325 at CopyTables1, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unicode conversion failed".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
Task failed: CopyTables1
how can I fix this error witout changing the language of the server?
Thanks.
The Kubyustus
I'm getting some data from a flat file with a SSIS Package, it comes a integer but I would like to converted to a decimal with a 3 scale.
Example:
Flat File: 2070015000950011800
In the data conversion I had it with a 3 scale, but what I got was this:20700.00015000.0009500.00011800.000But what I want is something like this:20.70015.0009.50011.800
I dont know if you guys get the idea. But I will apreciate if anyone can help me.
Thanks,
Erick
how to use SSIS to convert Julian date to Gregorian date. For example, julian 112001 to Gregorian 01/01/2012.
We are currently using SQL 2008 as the database for ERP/JDE tables.
As part of an ETL conversion I have dataflow task created that copies data from one table to another, we use it as a way to rename fields and change data types. What is strange is that the OLE DB SQL destination has not issue if I copy data from an int to smallint and in another case from an int to a numeric field and it runs fine. So I decided to test using a SQL Server Destination as I heard it may be faste that using OLE Db.
No I am getting the following error
Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID" can't be inserted because the conversion between types DT_I4 and DT_NUMERIC is not supported.
Error: 0xC02020F5 at Insert into TRANSACTION, SQL Server Destination [32]: The column "ID1" can't be inserted because the conversion between types DT_I4 and DT_I2 is not supported.
I do understand the error but not why it runs fine using the OLE DB but when running it through SQL Server Destination I get this error.
I am loading incremental data from sql server to oracle by using ssis and while data convert it says data type dont match.
SQL column data type is:smallint:SQL Server 2008 r2
Oracledata type is:Number(5):Oracle 10 g.
I am using SSIS to extract data from one oracle server to another. When i use this SSIS package in another Server, it gives me Unicode conversion error to non unicode for some Columns which are VARCHAR2 type. I have to then used drived column and use conversion, but my question is why this error from i migrate my SSIS package to another server.
View 1 Replies View RelatedI created a SSIS package moving data from a SQL 2005 table to an existing DB2 table on AS400 using Microsoft OLE DB Provider for DB2.
When the package was run, it showed that rows were successfully inserted to DB2. However, the data didn't seem to be converted correctly. Most of the string values were inserted as unusual characters. Also any string values of digits were not inserted.
For example, 1.) a character field (char(1) or nchar(1) as I have tried both types) in SQL 2005 table with a simple value of 'H' was inserted into the DB2 table field of type "A" (alphanumeric) of length 1 as 'ç' and others letters were inserted as other unusual characters. 2.) A string value of '00100' in SQL Server is not inserted to DB2 table at all.
Later we found that the fields inserted with usual characters are difined as CSSID =65535. A few fields with correct data inserted have CSSID=00037.
Does anyone know why this happened and how to solve this to get the data inserted correctly in the DB2 table?
Thanks in advance for any help!
I am using flat file as source.I have quantity column in flat file which is a  Numeric datatype and target table quantity datatype is Numeric.
I am able to load data from source to target but when I am comparing data from source to target I am not getting exact record from source flat files Source having data like
Source     >>  Target
31.61 Â Â Â Â Â >> Â 31.0000000000
00029.430 >> Â Â 29.0000000000
as we can see that data are not matching with source I can not change the target table quantity data type, is there any thing which I can do with source column data type.
Current situation:
Our data source is Oracle and there is a particular column, called number_of_units, with datatype numeric(28, 0) that we will extract into SQL Server data destination.
However, in our SQL Server data destination this column has a datatype numeric(34, 14). This is because we used this column to do some basic calculation (e.g. number_of_units/60) and we need to keep the decimals in tact.
In our current SSIS, we do a select query with CAST from the data source:
SELECT CAST(number_of_units AS NUMERIC(34, 14)) AS number_of_units FROM TABLE_A;
Will casting up from numeric(28,0) to (34,14) cause bigger number of bytes to be transferred across from the data source? I am talking about millions of records over here.
Are there other better ways of doing it?
1) Don't do a CAST in the select query (i.e. SELECT number_of_units FROM TABLE_A).
2) Add a derived/calculated column with numeric(34,14) in SSIS to fill in the calculation.
3) Lastly, load the calculation into SQL Server data destination.
I need to do EBCDIC to ASCII conversion in SSIS. The incoming data has packed decimal fields in it. Has anyone been able to convert packed EBCDIC decimal fields to ACSCII using SSIS?
View 7 Replies View Related I have a data flow task in which I have a ADO NET source and OLE DB Destination. I have in the ADO NET source a sql command which pulls all the columns in a table. My requirement is to ignore a particular column,say column99. I opened advanced editor and deleted the mapping between the external and output columns for column99. I had also set the Error and Truncation to "Ignore Failure" for column99. I had also mapped the destination column to <Ignore> in OLD DB destination.
But this still throws the error-
Description: The ADO NET Source was unable to process the data. Field table-column99 missing an escape character for a quote.Unable to update PK WHERE clause.Error processing data batch.
How do I solve this?
Hi All,
Here is a description of the issue I'm facing about decimal datatype conversion from DB2 to SSIS throught Microsoft OLE DB Provider for DB2.
I first discovered it when I tried to use a LookUp trans. and selected a decimal typed field. I was unable to validate it and clicking the OK button threw the error copied below:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [DTS.Pipeline]: The "output column "MFIXFRA" (317)" has a value set for length, precision, scale, or code page that is a value other than zero, but the data type requires the value to be zero.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204019 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Some workarounds on this issue led me to two other strange behaviours:
- If I create a dataflow task to perform a raw copy of a DB2 table to a brand new table in SQLServer destination (by clicking "new" instead of choosing an existing destination table), generated "create table" script for decimal fields is quite different from source table. For example, a source field declared Dec(15,2) is translated as Dec(29,5), and so on.
- Quite same behaviour if I try to derive a column using as derived column trans. If the source column is a decimal, it's scale and precision are interpreted ramdomly.
Any idea welcome
André