Hi ,
Just wondering what is the way to connect to Cobol VSAM files (data in array format) using SSIS ?
Where can I get the OLEDB/ODBC drivers for the same?
Thx,
Gaurav
Hi,
I have a need to convert sql server data to cobol format. Has anyone done this?
Hi
I am new to this forum and SSIS also.
I searched but could not find any answer here so I am posting my question.
We get some cobol text file that has Zoned Decimal also.
We want to use SSIS to convert the file in to SQL Server Database but we want to avoid using 3rd party s/w..
In DTS it was not possible..
Is it possible to convert Zoned Decimal to Decimal in SQL Server.
Thanks in advance.
PraRav
I'm new to SSIS, using Terdata Attunity connector for integrating data flow between Terdata (source) to SQL Server (Target).
SSIS package is getting failed because of length mismatch between source and Target for Unicode character datatype columns. Reason is Teradata TPT always occupies 3 times more length of actual defined in DB.Â
Even I tried by increasing length of attribute in Source but it didn't work.
I know by converting datatype from unicode -> Latin would work, but i don't want to do conversion since loosing some characters.
##################################################
Error isÂ
[Teradata Source [263]] Error: TPT Export error encountered during Initiate phase. TPTAPI_INFRA: API306: Error: Conflicting data length for column(5) - Â Source column's data length is (200) Target column's data length is (300).
##################################################
Is there a easier way to handle cobol book and file in SSIS ?Â
I have a file that has records with in one line and they are recurring. Â I am not sure how to explain but below is a sample format.
HeaderÂ
Account
Department
Header record1
Record 1Â
record 2Â
record 2Â
Record 1Â
record 2Â
record 3
Header record2
Record 1Â
record 2Â
record 2Â
Record 1Â
record 2Â
record 3
Hi,
I have a set of csv files and a set of Format Specification files for each of the csv files. I need to convert the csv files into another format of csv files as specified in the Format Specification files. All the columns of the input csv files do not have a mapping with the columns of the output csv files. How can I achieve this using SSIS ? This is an urgent requirement. Please reply asap. Thanks.
I have a problem with bcp and format files.We changed our databases from varchar to nvarchar to support unicode. Noproblems so fare with that. It is working fine.But now I need a format file for the customer table and and it is notworking. It is working fine with the old DB with varchar, but withnvarchar I'm not able to copy the data. The biggest problem is, that Igot no error message. BCP starts copying to table and finished withouterror message.This is my table:CREATE TABLE [dbo].[Customer] ([ID] [int] NOT NULL ,[CreationTime] [datetime] NULL ,[ModificationTime] [datetime] NULL ,[DiscoveryTime] [datetime] NULL ,[Name_] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,[Class] [int] NULL ,[Subclass] [int] NULL ,[Capabilities] [int] NULL ,[SnapshotID] [int] NOT NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NOTNULL ,[TargetRCCountry] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AINOT NULL ,[LocationID] [int] NULL ,[MirrorID] [binary] (16) NULL ,[DeleteFlag] [bit] NULL ,[AdminStatus] [bit] NULL) ON [PRIMARY]GOand this is the format file:8.0131 SQLINT 1 12 "#~@~#" 1 ID ""2 SQLDATETIME 1 24 "#~@~#" 2 CreationTime ""3 SQLDATETIME 1 24 "#~@~#" 3 ModificationTime ""4 SQLDATETIME 1 24 "#~@~#" 4 DiscoveryTime ""5 SQLNCHAR 2 510 "#~@~#" 5 Name_SQL_Latin1_General_CP1_CI_AS6 SQLINT 1 12 "#~@~#" 6 Class ""7 SQLINT 1 12 "#~@~#" 7 Subclass ""8 SQLINT 1 12 "#~@~#" 8 Capabilities ""9 SQLINT 1 12 "#~@~#" 9 SnapshotID ""10 SQLNCHAR 2 510 "#~@~#" 10 CompanyNameSQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 510 "#~@~#" 11 TargetRCCountrySQL_Latin1_General_CP1_CI_AS12 SQLINT 1 12 "#~@~#" 12 LocationID ""13 SQLBINARY 1 33 "#~@~#
"13 MirrorID """#~@~#" is the field terminator. We have a lot of text files with allkind of charachers in it. So we think this is a set that will neveroccur in our files.Thanks for your help!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
Hi,
I have downloaded SAP Connector and it works fine, its exactly what i need, but i have read that it's a beta and it's only available for 180 days... when it's supposed to be released a final version? it will have added costs to license prices? is any other alternative ( maybe a previous version of .net connector) to interact SAP with SSIS?
Thanks
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current
SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
I am using bcp to execute a query and would like to use the -f option for format_file so I am going thourgh the books on line and trying to make some sense out of it,no quite clear on it at the moment.
How do I find out the version of bcp utility and would somebody have an example with this options just to see how it works.
Thanks
I'm not "cobol person" but now, I have to bind a cobol application (from mainframe) to query SQL 2005
Have somebody had this task?
This nifty function convert COBOL value to equivalent integer.
If last characer is one of these alphanumeric charactersJ -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
} -0it means that complete value is negative and last character is the equivalent numeric in table above.CREATE FUNCTION [dbo].[fnCobol2Int]
(
@Item VARCHAR(18)
)
RETURNS BIGINT
AS
BEGIN
RETURNCAST(
CASE
WHEN @Item LIKE '%[^}jklmnopqr0123456789JKLMNOPQR]%' THEN NULL
WHEN LEFT(@Item, LEN(@Item) - 1) LIKE '%[}jklmnopqrJKLMNOPQR]%' THEN NULL
WHEN RIGHT(@Item, 1) LIKE '[}jklmnopqrJKLMNOPQR]' THEN '-'
ELSE ''
END + LEFT(@Item, LEN(@Item) - 1) + CHAR(47 + CHARINDEX(RIGHT(@Item, 1), '}JKLMNOPQR0123456789}jklmnopqr') % 10) AS BIGINT)
END
E 12°55'05.25"
N 56°04'39.16"
Dear Forum Members.
I am currently using Visual Studio 2005: Business Intelligence Development Studio to create an Integration Services Package.
The package contains several containers, within each container there is a €˜foreach loop container€™ and within the €˜foreach loop container€™ there is a script component, execute component and another script component.
Each of the three components is connected together by single connectors.
My issue arises when I disable a container and then enable the container; the connectors between the components disappear (e.g. are not visible) but do exist as I can not create another connector between the components. When I attempt to create the connector again between the components I receive the error message. This tells me the connectors are present but not visible.
TITLE: Microsoft Visual Studio
------------------------------
Cannot create connector.
Only one workflow can exist between the same two executables.
------------------------------
BUTTONS:
OK
------------------------------
Can someone kindly explain why this is happening and how to resolve this issue?
I can not change the connector types from success to completion or vice versa because the connectors are not visible for me to select them.
Many Thanks
Hi
I need to make changes to a bcp format file, but don't understand the thing.
Format file =
6.0
10
1 SQLCHAR 0 3 "" 1 Col1
2 SQLCHAR 0 9 "" 2 Col2
3 SQLCHAR 0 1 "" 3 Col3
4 SQLCHAR 0 1 "" 4 Col4
5 SQLCHAR 0 1 "" 5 Col5
6 SQLCHAR 0 1 "" 6 Col6
7 SQLCHAR 0 136 "" 7 Col7
8 SQLCHAR 0 50 "" 8 Col8
9 SQLCHAR 0 50 "" 9 Col9
10 SQLCHAR 0 50 "" 10 Col10
Example of the data extract file to be imported (.txt) =
AB 1 01A Surname1 Mr NameOne MiddleNameOne AddressLineOne Testing 1 AddressLineTwo Testing 2 AddressLineThree Testing 3
CD 1016 01A Surname901 Ms NameNineHundredAndOne MiddleNameNineHundredAndOne AddressLineOne Line 2 Testing 4 AddressLineTwo Line 2 Testing 5 AddressLineThree Line 2 Testing 6
I understand the first 6. Col1 has a lenght of 3, col2 has a length of 9 and so on.
When I get to field order 7 (col7), I get stuck. Field order 7 should be the Surname, title, name and middlename of the customer. But the host file data length is 136.
From what I understand that would mean that it would included the first part of the AddressLineOne as well isn't it? The thing is that it is working currently (not reading the address)
I'm obviously not understanding how this works.
Can anyone help with this?
I developed a database in MS SQL server7. But the main database was developed in COBOL. Is there anyway I can upload my
database to mainfram? Thank you in advance.
This script generates a format file for every table in a database.
It's set up for fixed width files, not too common in this world...but they can easily be modified.
Also the do not at this time handle text or image columns.
Any hints/advice here would be great.
I'll post it when I figure it out.
happy bcping..
SELECT FORMAT_CARD FROM (
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
ELSE 26
END)
+ char(9)+'"
"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
Brett
8-)
SELECT @@POST=NewId()
That's correct! It's an AlphaNumeric!
How to convert SQL trace files into excel files without doing any work on SQL Profiler / SQL server using any scripting code.
Consider that we only have SQL Trace files. What are the steps involved in converting into .CSV format using single "CLICK"
Thanks,
Rajaram.
Chennai- India.
I used bcp to produce the apended format file.How can it be modified to recognize the quotes that surround the textfields and not insert the quotes along with the text? Invariably, thefirst four columns have text surrounded by quotes and are terminated bytabs. If the first column has "abc", only abc ought to be insertedinto that field in the table.ThanksTed==================format file========================<?xml version="1.0" ?>- <BCPFORMATxmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">- <RECORD><FIELD ID="1" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="6"COLLATION="Latin1_General_CI_AI" /><FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="7"COLLATION="Latin1_General_CI_AI" /><FIELD ID="3" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="48"COLLATION="Latin1_General_CI_AI" /><FIELD ID="4" xsi:type="NativeFixed" LENGTH="4" /><FIELD ID="5" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="8"COLLATION="Latin1_General_CI_AI" /><FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1" /></RECORD>- <ROW><COLUMN SOURCE="1" NAME="f_supplier_code" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="2" NAME="f_product_code" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="3" NAME="f_product_name" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="4" NAME="f_asset_classes_id" xsi:type="SQLINT" /><COLUMN SOURCE="5" NAME="f_size" xsi:type="SQLVARYCHAR" /><COLUMN SOURCE="6" NAME="f_dist_unit" xsi:type="SQLFLT8" /></ROW></BCPFORMAT>
View 6 Replies View Related
Hello
I have a working package. It imports a file. The file import uses a file connection that points to one file. This file connection contains all of the file layout and field information like the name, length etc. This information is for 85 columns.
I would like to change the file connection to a source of a SQL task (or a for loop task). This SQL task will get the same file type but there will be multiple instances of the file (different names.)
What is the best way to migrate this current Flat File Source so it can now take different names and yet not loose the current field mapping that the current flat file source contains? Is there a way to accomplish this?
Thanks
Hi all,
I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).
I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.
I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.
Any help will be appreciated.
Kind regards,
Sébastien.
Hello,
I need to create about 1,000 (literately) Excel files that each contain 5 tabs. The data being placed on the tabs will always be the same (meaning the columns are static).
I am fairly advanced at Excel VBA so I can write code that does all the following in Excel (looped 1,000 times):
Open an Excel template
Bring data in from the tables
Filter, then copy-paste the appropriate rows into each tab.
Save the new Excel file.
Email the file to appropriate individual (it is a Microsoft Exchange Server).
As I started this in VBA, I thought that I might be able to do it with SSIS. My concern is I need to have the rows formatted (font, border, etc.) and the number of rows change.
My questions are:
Is it possible to format Excel with SSIS?
Can I email the files even if it is not with an SMTP protocol?
Would SSIS process this data faster then Excel?
Does this approach even make sense? Am I better just doing it with VBA?
Thank you for the help.
-Gumbatman
I have an SSIS package that successfully runs a Teradata connection query. However, when I schedule the job to run in the SQL Server Agent I get the following error:
Executed as user: SERVER_NAMEAdministrator. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation.
All rights reserved.   Started: 7:53:26 AM Error: 2015-06-08 07:53:26.78    Code: 0x00000216    Source: Package1 Connection manager "Teradata"
[Code] ....
Why would a connection work in SSIS but not in a scheduled SQL Server Agent job?
I can't find a Dynamics CRM topic and I'm hoping someone here will recognize the problem that we are having with integration between Dynamics and SRS.
Dynamics CRM server deployed fine. But we are having this problem when trying to install the Dynamics SRS Data Connector (an additional component that is supposed to be installed locally on the SRS server):
http://support.microsoft.com/kb/947060/en-us
Trouble is, the SQL server is a default instance, and SRS is hosted on a default web site listening on port 80. In fact, this is just a plain default install of SQL 2005 and SRS 2005 on one server. Nothing has been customized. And yet we get this error when we try to install SRS Data Connector on the local SRS machine:
"Unable to validate SQL Server Reporting Services Report Server installation. Please check that it is correctly installed on the local machine."
But the SRS is installed and running fine! I can even see and run reports that the Dynamics CRM Server install had created on the SRS server! And yet the setup program insists that SRS is not installed and it won't proceed with installation. It obviously runs some strange prerequisite check. (Devs: Why not just run a connect test to the SRS Web service?)
Environment: Win 2003 native domain on server A, Dynamics CRM 4.0 Pro on server B (Win 2003 VM 32bit), SQL 2005 SP2 and SRS 2005 SP2 on server C (Win 2003, physical server, 64bit). Data Connector failing to install on server C. I've even tried using a domain admin account, and went as far as running the installer in SYSTEM context. Still it fails.
I've also tried every variation of SRS and IIS configs that I know of (disabling SSL, enabling anon auth, etc etc etc etc...) I've gone as far as setting up a separate domain, separate SQL and SRS, and making a local Dynamics install, and STILL I got the same error while installing the Data Connector.
This is most frustrating. There's obviously something wrong with the method Dynamics CRM setup program uses to determine whether or not SRS is installed. But how come there isn't any info about this except for that one KB article that doesn't apply to us?
Please if you know anything post a reply! Thanks.
I am a novice in data base designing. I have MS certification in both design, implementation and administration. Can anyone help me provide information regarding converting from Cobol flat files to sql server tables ?
I will be very grateful.. This is my first assignment.. Got to prove myself... help....
In DB2 stored procedures are calling external cobol code/program, I want convert DB2 stored procedure to MS SQL stored procedure, on that case, How to call call cobol from MS SQL stored procedure.
View 7 Replies View RelatedI am using SSIS to load raw files into database. In my files I have columns Date which has format
1/1/2010 12:00:00 PM.
I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.
Hello,
Here's my need :
In one directory there's several excels file. Theses file have the same structure :
col : LOOKID, LOOKNAME, ASMPID, ASMPNAME, LOOKTYPE, LTYP_NAME, PAR_TYPE, PTYP_NAME, PARAMETER, VALUE.
The problem is that the cell format of the PARAMETER col. is different bewteen excel files. It could be date, numeric, ... The col destination in sqlservr database is Varchar(10).
I've created the ssis package with a ForEach Loop, and in the ForEach loop I've created a Data Flow Task.
In the data Flow Task I've created an excell source file (using excel file with col PARAMETER in date format) with an OLE DB destination.
When I launch the package on the same excel file as the one using to create Excel Source object it's OK, no errors, and data in the sql table are OK.
But when I launch the package on Excel file with col. PARAMETER in numeric format, there's no execution errors, but in the destination table the value of the PARAMETER col. is transform in date format.
I tried to change in Excel source object the datatype of the input PARAMETER col, but I've got some compilations errors. It seems that SSIS recognize automaticaly excel source data type col. But may be I did something wrong in the excel source settings ?
Is there a way to force the excel source datatype with varchar(10) ?
I've also tried to do the treatment with an script task but my vb.net knowledge isn't enought to do that.
If you have some suggestions, I'm listening.
Thanks
Luffy
Here is my idea but I am looking for the best practice.
Each record can have 3 possibilites.
I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.
Any other ideas?
Thanks,
Larry
We are importing xer formats through the wizard to sqlserver database and It takes upto 35-45 mins for each import (single project), any option to reduce the time.Is they any other import options - which can give us faster results.
View 0 Replies View RelatedHi All,
How to create a text file in UNIX format using Flat File connection manager. By default when we create a connection manager for flat files it is taking "CRLF" as the delimeter.
I beleive the format of the file will be decided based on the control line feed character at the end of each row. There are different control line feeds for different operating systems.
CR - Mac OS (Carraige return)
LF - UNIX (Line Feed)
CRLF - Windows. (Carriage return Line Feed)
Correct me if i am wrong.
Thanks in advance.
Hi
I am currently going through a DTS package which I use to build my DW after changes all the connections I fired it and
"Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly,
parameters not set correctly, or connection not established correctly.
Any ideas?