Hi,
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
Thanks,
Jeff
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?
View 9 Replies View RelatedHi,
I'm inserting records into a Table1 with the following fields :
tabID int 4ClientID int 4..etc etc.
In the insertitem template, I want the user to enter a Clientcode into textbox1. Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1.
I've got a mental block and I cannot see how I can do that !
Any ideas most gratefully appreciated.
Thanks
Sunil
I've two table.......
One table Name Form1
Ex
----------
Name|F1No
----|-----
23wa|1
xyzx|2
abcd|3
......
......
......
---------
F1No====>primary Key
Second table Name Form2
Ex
--------
F2No
----
3
1
2
2
2
1
......
......
......
--------
F2No===>Foreign Key
i want top 3 Name(field) in first table(Form1).
Result
------
xyzx
23wa
abcd
hi
i have 2 tables in DB. followind is the schema :
tblPostingAccount(PostingAccountCode,Description,type,OpeningBalance, Balance)
tblDetails(PostingAccountCode,debit,credit,VoucherNo)
i want to show all posting account codes from tblPostingAccount and their respective sum of debit and credit columns ..
like this
PositngAccountCOde,Debit,Credit,OpeningBalance,Balance
1-0-1-0-1 7000 6799 699999 9000000
1-0-1-0-2 7000 6799 699999 9000000
1-0-1-0-3 7000 6799 699999 9000000
1-0-1-0-4 7000 6799 699999 9000000
1-0-1-0-5 7000 6799 699999 9000000
1-0-1-0-6 Null Null 699999 9000000
the last code has nothing in tbldetails table ..i want a query from which i could show results as above ..
Plz help . Thanks in Advance .
View 3 Replies View Related
Hi all
I am trying to teach myself SSIS and need some help, please.
I have SQL table A as my source with column X. I need to add column Z and populate it from SQL table B, column Y where table A column X >= table B column R and <= table B column S.
Thus: Populate A.z with B.y where A.x >= B.r and A.x <=B.s
The interval between B.r and B.s is unique and only one value will be returned.
Which is the best way to do this in SSIS?
Thanks,
NewToETL
Hi,
Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?
Example:
Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF
I want to extract the postcode from this field into another field in another table....
Example 2:
Name
-----------
Ivor Smith
I wish to strip the last name from the field, and put this into a field in another table..
David
There is a simple SQL table ("mytable"). Say, it has 100 rows and 5 columns. One of the columns (say "time") contains timestamps across the whole day and the data in this column has the following format: hh:mm:ssAM/PM. So, the table looks like this:
time var1 var2 var3 var4
12:00:01PM value ...
12:00:05PM value
12:00:08PM value
12:00:20PM value
12:10:12PM value
...100 rows
How to create simple SQL request for extracting data between any 2 timestamps? For example, I need sub-table of the initial table containing all data values between 12:00:05PM and 12:00:20PM:
time var1 var2 var3 var4
12:00:05PM value
12:00:08PM value
12:00:20PM value
I consider something like this:
SELECT * FROM mytable WHERE time BETWEEN ...?
Let us suppose I've 3 tables in which 2 columns data are same in all 3 tables & rest fields are distinct
Table 1 :
ID1 ID2 Gender Code towncode
1 12 1 234 1234
2 13 2 543 5463
89 187 1 125 N21Q
Table 2 :
ID1 ID2 Relation Name DOB
1 12 13 XYZ 21/01/1967
1 12 1 QAS 01/10/1987
1 12 2 NHS 09/12/2001
2 13 1 NHG 10/01/1987
2 13 2 GHS 16/12/1999
89 187 2 KJA 31/03/2000
89 187 1 KLA 09/11/1986
89 187 5 KOP 19/12/2001
Table 3 :
ID1 ID2 Period Date
1 12 1 01/01/2011
2 13 1 01/01/2011
89 187 2 01/01/2011
I want result as :
ID1 ID2 Gender Code towncode Relation Name DOB Period Date
1 12 1 234 1234 13 XYZ 21/01/1967 1 01/01/2011
1 12 1 234 1234 1 QAS 01/10/1987 1 01/01/2011
1 12 1 234 1234 2 NHS 09/12/2001 1 01/01/2011
2 13 2 543 5463 1 NHG 10/01/1987 1 01/01/2011
2 13 2 543 5463 2 GHS 16/12/1999 1 01/01/2011
89 187 1 125 N21Q 2 KJA 31/03/2000 2 01/01/2011
89 187 1 125 N21Q 1 KLA 09/11/1986 2 01/01/2011
89 187 1 125 N21Q 5 KOP 19/12/2001 2 01/01/2011
May be some duplicate entry of same record is available in Table 1.
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.
View 7 Replies View RelatedBeing 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?
View 2 Replies View RelatedI need to extract data from SSAS' cubes into a SQL Server table.
I already read examples using Linked server (with openquery), SSIS, etc. However, the result always return as many columns per dimension as levels. I need to extract all members of a dimension in a column. E.g., when excecuting the following MDX query in Adventure Works 2014:
select [Measures].[Sales Amount] on columns,
    Non Empty [Date].[Calendar].members on rows
 from [Adventure Works]
I would like to get this result (MDX query in SSMS), but with keys displayed intead of names:
But I get this (MDX thru openquery or SSIS):
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?
View 2 Replies View RelatedWhat 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
View 1 Replies View RelatedHi,
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.
I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.
Column searchpath has value like:
//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model
I need to extract the folder names and display it in separate columns in a new table this way:
AFR AFRABC CDD Packages Deployment XXX spend name
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I'm using the following syntax to extract a list of all the table names on a linked server:
EXEC sp_tables_ex
@table_server = MY_SERVER_NAME
It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?
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
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?
Thanks, in advance.
Hi,
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?
Thanks
Can I get drivers to setup Replication to Mainframe . I really don't want to install HIS
Thanks
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
View 1 Replies View RelatedHello 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:
1432 1138734217 1144256628<CRLF>
1432 1138734217<CRLF>
1433 1136657788 1122441177<CRLF>
1433 1125554545 1122441177<CRLF>
1433 1192925544 1122441177<CRLF>
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
Hello,
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.
Username: imtheuser
Password: pwd
Source file: c:myfile.txt
Destination: 'PROD.ABC.DAILY.BATCH'
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
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Thanks again.
I'm trying to implement DTS and FTP. So that when a company sends or FTP infomation to our server, it runs a script and checks to see if the infomation is correct and sends and email saying successful or unsuccessful.
View 1 Replies View RelatedHi,
I have a string almost 11006 length.. when i try to send back from SQLCLR procedure
it says cannot send ..
here is Exception Text "Message length 11060 exceeds maximum length supported of 4000."
Max limit to send a string using pipe is 4000
How I can send a string which is large in size than 4000.
Thanks
Hello All,
Hopefully someone out there will have an idea as this is driving me nuts.
I want to send a dynamic files in attachment files ny send mail task that file name has change follow datetime.
I try to use the expression but I can't use it.
please tell me for this problem.
Any suggestions appreciated,
Thanks.
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.
Thanks, MaryOS
I have below code which send email from SQL Table "testing$" with use of store procedure but I am not getting any email, please check where is the error and if I run below code as query then it give me expected result but mail not received,
[CODE="other"]
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
DECLARE @body_format nvarchar(max)
SET @tableHTML =
[code]....
Hi folks,
I'm a novice in sql server, I would like to
know how to convert a table into a text, that is,
i actually want to send a table by mail, and i think i can't really send the table itself, i have to convert it into text,i'll appreciate if someone can show how to write the stored procedure for that,
thanks a lot,
Pitter
Hi guys,
I am new to reporting services. I want to know how to send email whenever there is an update on TABLE_Customer. I have looked through data-driven subscription, but that is just normal schedulling. When i surf the net, i also found UpdateReportExecutionSnapshot. can i just put UpdateReportExecutionSnapshot inside my application so that when on UPDATE function, i will call UpdateReportExecutionSnapshot to trigger the snapshot to be updated?
Help... Thank you very much