I am trying to use the FTP Task within a DTS Package to copy a file from the Mainframe (OS390). We have SQL2000 with SP2 running on NT. This process works when I do it manually with ftp. At first, when I would execute the package it would just hang like it is copying but it would never end.
Then, I added a Dynamic Properties Task to the package. I used the Dynamic Properties Task to set up a Constant with the actual name of the Source File located on the Mainframe. When I ran this I received "Invalid string format for source file name".
Next, I removed the Dynamic Properties Task from the DTS Package, saved the package, and executed it. The package executed successfully, but when I looked in the folder on my hardrive the file that was supposed to be copied was not there. In order to get the package to execute successfully it appears that I have to go through the following process: add the Dynamic Properties Task, set up a constant to change the Source File name, save the package, delete the Dynamic Properties Task, save the package and then execute it.
Also, on one execution of this package, it brought across the file from the Mainframe to my hard drive but the file was in Binary format.
What do I have to do to get the FTP Task within a DTS Package to work? Would you specify in detail exactly how the Dynamic Properties Task needs to be set up? How can I specify how to change the file format from Binary to ASCII in the DTS Package?
Has anyone had any success sending or receiving file(s) from either Script or FTP task? I've Google and found examples and no luck for me. The main idea is to send a file from local PC/server to mainframe.
I've used this workaround, SSIS Script Task but no good.
SQL Server Feedback Workarounds 281893. SSIS FTP Task - Mainframe When you try to connect to a mainframe (os / 390) to ftp receive a file you get an error message stating that the path does not begin with a "/". Active feedback entered 6/7/2007 by EWisdahl
Entered by EWisdahl on 6/7/2007
Add a script task (as follows) to download the desired files... ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main() Try 'Create the connection to the ftp server Dim cm As ConnectionManager = Dts.Connections.Add("FTP") 'Set the properties like username & password cm.Properties("ServerName").SetValue(cm, "myServer") cm.Properties("ServerUserName").SetValue(cm, "myUserName") cm.Properties("ServerPassword").SetValue(cm, "myPassword") cm.Properties("ServerPort").SetValue(cm, "21") cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb cm.Properties("Retries").SetValue(cm, "1") 'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing)) 'Connects to the ftp server ftp.Connect() ftp.SetWorkingDirectory("MyFolder.MySubFolder.MySubSubFolder")
Dim files(0) As String files(0) = "MyfileName" ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection ftp.Close()
'Set the filename you retreive for use in data flow Dts.Variables.Item("FILENAME").Value = maxname Catch ex As Exception Dts.TaskResult = Dts.Results.Failure
In using SSIS to migrate data from mainframe to SQL 2005, I had a situation where only group level data was exposed through the ODBC to SSIS, so I pulled this information as varchar on the SQL destination side. Now I would like to break that group into the individual numeric columns I need on SQL Server. However, the positive and negative sign did not convert because it came of character. I can write something to convert the positive signs to positive numbers; however I cannot do the negative because I would need get rid of the leading zeros in order to place the negative sign before the number. Is there anything I could have done to get SSIS to do the conversion like it did for every one-to-one mapping?
When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".
Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.
Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.
Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?
Has anyone been able to use an ftp task to pull a flat file from a z/os mainframe? The ftp task appears to want the remote file to begin with a /, which pulls you into unix system services on z/os.
Being that this issue first surfaced September 2005 and it's now six months later (March 29, 2006), has the problem with recognizing VMS datasets (not requiring a '/') been addressed?
We would like to use DTS to pull data from DB2 on the mainframe into SQL Server 7.0 databases. Does anyone know what 3rd party products are required for us to do this (and which ones are the best to use)? What has to be installed on the mainframe and on the SQL Server?
What would I need in order to setup replication between SQL2000 SP3 and MVS Subsystem running Db2 database . I mean, does it need any 3rd party tool or regular replication setup between SQL and Db2 is enough
I am trying to send files over to an ftp server on Mainframe using scripts that is provided in this link, but I can't get it to work. The problem is the sendfile method concatinates the source file name to the MVS dataset name which causes the issue.
Here's my code (FTPConn is a connecation mgr for FTP):
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim mgr As ConnectionManager
mgr = Dts.Connections("FTPConn")
Dim conn As FtpClientConnection
conn = New FtpClientConnection(mgr.AcquireConnection(DBNull.Value))
Dim toFiles As String
toFiles = "ftpp.a.b.xxxxx.yyyyyy.int"
Dim fromFiles(0) As String
fromFiles(0) = "c: est.txt"
Try
conn.Connect()
conn.SendFiles(fromFiles, toFiles, True, True)
Catch ex As Exception
Finally
conn.Close()
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Output:
SSIS package "Package.dtsx" starting.
Error: 0xC001602A at Package, Connection manager "FTPConn": An error occurred in the requested FTP operation. Detailed error description: 200 Representation type is Ascii NonPrint
200 Port request OK.
501 Invalid data set name "ftpp.a.b.xxxxx.yyyyyy.int/test.txt". Use MVS Dsname conventions.
hi I am loading from Mainframe to Sqlserver. Right now i am truncating the tables in sqlserver connecting to mainframe and downloading the tables to Sql server. The problem is sometimes i fail to connect to Mainframe. So is there any way of checking whether i can connect to mainframe and if succesful truncate the tables. Thanks
is there a solution outside for getting of DB2 for OS/390 into SQL server? I mean not replication or copying of data with flat files or an ETL tool, but a kind of integration of DB2 tables as they would be "normal" mssql tables.
Oracle has Transparent Gateways for os/z DB2 and many other non-Oracle databases, is there some similar for SQL Server?
Could it be realisable with an ODBC client for DB2?
I have a million record mainframe flat file that I BULK INSERT into aSQL table with CHAR(fieldlength) deined for every column to preventimport errors.Once imported I "INSERT INTO ... SELECTdbo.MyScrubFunction(columnN),..."My scrub functions will take for example a char(8) YYYYMMDD date fieldand return either a valid datetime variable or a NULL for 8-spaces or8-zeros....or return a MONEY datatype by dividing by 100.PROBLEM: This is extremely SLOW!QUESTION: Should I do multiple "UPDATE ...SET" statements back intoCHAR() columns, then let SQL Server do the CHAR() to DATETIME andMONEY conversions itself? What is the most efficient or recommendedmethod to transform/scrub imported data?P.S. I also have to convert low-values and reformat dates which Ialso use my own UDFs for.THANKS
Hello Everybody! Im trying to migrate my SQL 2000 packages that´s currently working in the company production enviroment to SSIS packages. So, in the 2000 version i got the flat file from Mainframe and i had to do a trick to transform all the columns to match the same size as the example above:
So, when i import the file, first of all i have to transform the Text file to another texfile file fixing the size to 32..... but you can see that in the second row im receiving a CRLF and if i try to import without the trick, the preview of the ragged file show me the columns desorganized...
Someone knows how to import it without transform to another text file with fixed length? Thanks Cleber
I want to extract all fields from a table and ftp the output file to the mainframe. My problem is the columns in the extracted file do not line up, which is required by the mainframe program. Example column would be this column of last names: Smith Jones Sullivan
How could I format the extracted data to look like this: Smith Jones Sullivan
I have data on the mainframe that contains special characters such as a copyright symbol (letter c with a circle) when running the SSIS package migration from mainframe database to SQL Server 2005 database the data in the column is truncated at the point it hits this symbol with no errors. I had error reports set up for truncation and did not see an error report for this.
I have tried to change the data type to different data types and re-run nothing changes things.
The symbol is a EBCDIC 'b4' on the mainframe side - I have no idea what that would translate to.
Anyone have any ideas? Am I stuck with substituting something else for the symbol on the mainframe side and re-running the package? Is there anyway to handle this situation in SSIS? I am using the Import/Export Wizard for this one because before I ran into this problem it seemed to be working fine.
I am migrating mainframe data to SQL Server 2005 and have found that from a mainframe character field with leading zeros for example the value of 00023 to a SQL Server column defined as varchar (5) the resulting column value is 23 not 00023. I need the leading zeros because these are account ids, etc. So the value is 00023 not 23. Is this some setting in SQL Server 2005 that needs to be changed or what? This is not a numeric field on the mainframe or a numeric column for SQL Server.
I've seen several posts about reading and writing files that have different record types with varying column metadata. My particular file has 11 record types plus several header types and looks something like:
<Header1>
<Header2>
<Detail01-#1>
<Subdetail02>
<Subdetail03>
...
<Detail01-#2>
<Subdetail02>
<Subdetail03>
...
...
Since i need to get different detail and subdetail records, i can't really use the technique of 3 dest file connection managers found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87269&SiteID=1
I've tried using an exec sql to get the main detail records and then a forech ADO en umerator that would get the subdetails, but it all seems so kludgy. I'm starting to think that I should just write the bulk of the file creation code in a c# app instead of trying to smush this into SSIS. Opinions? Am I missing some trick in SSIS?
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
I'm using SSIS in Visual Studio 2012. My Execute SQL Task calls a Stored Procedure where I have a TRY-CATCH. Last week there was a problem and the CATCH was executed and logged an error to my error table, but for some reason the Execute SQL Task didn't fail. Is there a setting to make the Execute SQL Task fail when an SP encounters a failure?
I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error:
The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer.
Then when I try to delete it it gives this other error:
Cannot remove the specified item because it was not found in the specified Collection.
I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition.
Any suggestions why this is happening and how to fix it?
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
I am using SQL 2005 SSIS. I am joining several large tables and then the move result into another table in the same database.
I would like know which method is faster:
Use Execute SQL Task to insert the result set to the target table
Use the Data Flow Task to insert the result set to the target table. (Use OLE DB source to execute SQL command and then use the SQL destination) Could you tell me why then other is slower?
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
In the Control flow tab, I have an Execute SQL Task that outputs full Result set into a variable of an object type. Now how can I write the contents of the Full Result Set into a text file using Script Task. I also want to format the following way while I output into a file:
Column Name 1 : Column Value
Column Name 2: Column Value and so on
I tried writing the contents of the Object Variable into a file, but the file had an output of single word: System.__ComObject.
Code for Writing the Full Result Set into a Text File
Dim RSsqloutput as String = Dts.Variables("objVariable").Value.ToString
Dim strVal as String = "File completed on " & Now() & vbCrLf & "------------------------------------------------------" & vbCrLf
In short, does the €śTransfer SQL Server Objects Task€? support distributed transactions?
In trying to use a €śTransfer SQL Server Objects Task€? in a container using a transaction on the container. The task is set to support the transaction. It is setup to copy table data from several tables from a non-domain server (sql server 2000) to a domain-based server (sql server 2005). I get an error stating, €śThis task can not participate in a transaction€?.
I am wondering if it means exactly what it says €“ this task in SSIS can€™t participate at all. Or does it mean that it won€™t in this scenario for some reason. I attempted a simple copy of data from mssql 2005 to mssql 2005 (same server) and the task still failed). MSDTC appears to be running properly on my machine and such (I can do a simple distributed transaction across linked server to the 2000 server in Query Analyzer (QA)). Also, MSDTC appears to be working on both servers with distributed transaction query tests in QA.
Here€™s the error info€¦
SSIS package "Development BusinessContacts and Products Migration.dtsx" starting. Information: 0x4001100A at Copy BusinessContacts Data: Starting distributed transaction for this container. Error: 0xC002F319 at Copy BusinessContacts database table data 1, Transfer SQL Server Objects Task: This task can not participate in a transaction. Task failed: Copy BusinessContacts database table data 1 Information: 0x4001100C at Copy BusinessContacts database table data 1: Aborting the current distributed transaction. Information: 0x4001100C at Copy BusinessContacts Data: Aborting the current distributed transaction. SSIS package "Development BusinessContacts and Products Migration.dtsx" finished: Failure. The program '[4700] Development BusinessContacts and Products Migration.dtsx: DTS' has exited with code 0 (0x0).
I have an application like fetching records from the DataBase(MS Access 2000) and results i have to use in Script Task. At present i have used the record fetching query,connection string in Script itself. I would like to use in Independently. Is there any Tools like (Control Flow Tools like Execute SQL Task) are there to fetch the result set from Acccess and can use the fetching results in Script Task....
I have a stored procedure that is executed via a sql script task that returns a full result set. I map this result set to a variable or object type. Is there a way to use this variable as a data source in a subsequent data flow task?
I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.
Now I want to add a functionality :
If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop
if failure mark all tasks at enable and start extracting data and continue further with execution.
I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.
For first time I'm testing this task and surprisingly, when I try "Edit Package" option:
1)The DTS host failed to load or save the package properly 2)The selected package cannot be opened 3)Error HRESULT E_FAIL has been returned from a call to a COM component
But after these messages you can see all the tasks but they haven't name!!
It seem as if RCW mechanism has failed between managed and unmanaged coded-partially.
I don't dare to follow doing more stuff, I don't know if that package is well-loaded or not from there. ?ż
2: Dataflow Task: Datareader--Script componant--OLE DB Destination (SQL Server 2005--a single table --always around 600,000 rows)
How do I set up a transaction where if there is a failure the Truncate Table command will roll back---and the OLE Destination (A single SQL Server table) will be left the same as before the load started.
Another question with that volume of data --600,000 rows will a truncate table be pratical in a transaction
HI, I need to trigger some packages upon existance of specific files in a particular directory. Sound lkike the file watcher task (from SQLIS) would do the job but I am wondering what is the difference of using this tool instead of a for each loop container. I mean, If a file exists in a directory, the for each loop container will detect it. Since the file watcher is not a service, the package containing it needs to ne scheduled on a regular basis for the filewatcher to detect the file, right? So, a for each loop container would do the job? So, waht wouldbe the advantage of using the file watcher task?
