VSAM Cobol Files (Array Format): Connector For SSIS

May 14, 2007

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?



View 3 Replies


Sql Server Data To Cobol Format

Apr 16, 2007

I have a need to convert sql server data to cobol format. Has anyone done this?

View 3 Replies View Related

How To Convert Cobol ZONED Decimal Using SSIS

Mar 14, 2007


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.


View 17 Replies View Related

Integration Services :: Teradata Attunity Connector In SSIS For Unicode Characters

Nov 14, 2015

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).

View 3 Replies View Related

Integration Services :: Processing COBOL Copy Book And File With SSIS?

Nov 19, 2015

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 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

View 9 Replies View Related

Converting Csv Files From One Format To Another Format With Differing Columns

Dec 19, 2007


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.

View 1 Replies View Related

Bcp Unicode Files Using Format Files

Jul 20, 2005

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!

View 2 Replies View Related

RS2005: Export To Excel Error: Destination Array Was Not Long Enough. Check DestIndex And Length, And The Array's Lower Bounds.

Jan 25, 2007


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.


View 10 Replies View Related

SAP .Net Connector 2.0

Oct 16, 2007

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?


View 4 Replies View Related

How Would I Send A String Array As A Integer Array?

Jun 25, 2007

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)

View 5 Replies View Related

HELP!!! Cannot Import SSIS Package Files From .dtsx Files

Oct 8, 2007

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!!!!

View 20 Replies View Related

Bcp Format Files Help?

Jun 3, 2004

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.


View 4 Replies View Related

Cobol -&> SQL2005

Sep 22, 2006

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?

View 4 Replies View Related

Cobol Conversion

Aug 23, 2007

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)
WHEN @Item LIKE '%[^}jklmnopqr0123456789JKLMNOPQR]%' THEN NULL
WHEN LEFT(@Item, LEN(@Item) - 1) LIKE '%[}jklmnopqrJKLMNOPQR]%' THEN NULL
WHEN RIGHT(@Item, 1) LIKE '[}jklmnopqrJKLMNOPQR]' THEN '-'
END + LEFT(@Item, LEN(@Item) - 1) + CHAR(47 + CHARINDEX(RIGHT(@Item, 1), '}JKLMNOPQR0123456789}jklmnopqr') % 10) AS BIGINT)
E 12°55'05.25"
N 56°04'39.16"

View 1 Replies View Related

Connector Between Executables Dissapears

Jan 31, 2007

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.



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

View 4 Replies View Related

Structure Of Non-XML Format Files

Apr 10, 2007

I need to make changes to a bcp format file, but don't understand the thing.

Format file =
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?

View 3 Replies View Related

Upload Database From Sql 7 To COBOL???

Oct 21, 1999

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.

View 1 Replies View Related

Generate Format Files For All Tables In A Db

Sep 16, 2003

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..

, TABLE_NAME, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
, c.TABLE_NAME, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
AND c.table_schema = t.table_schema
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
+ char(9)+'"
, c.TABLE_NAME, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping




That's correct! It's an AlphaNumeric!

View 1 Replies View Related

Convert SQL Trace Files Into .CSV Format

Mar 1, 2007

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"

Chennai- India.

View 2 Replies View Related

Format Files For Use With Bulk Insert

Aug 9, 2006

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

Question: Flat File Connector Reconfigure

Mar 20, 2008

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?


View 5 Replies View Related

How To Manage Different Input (Excel Files) Format

Jun 21, 2006

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,


View 3 Replies View Related

Create, Insert And Format Into 1,000 Excel Files

Feb 24, 2008


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.


View 4 Replies View Related

Integration Services :: Teradata Connector Doesn't Run As A Scheduled Job

Jun 8, 2015

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?

View 2 Replies View Related

SQL Reporting Services Data Connector For Dynamics CRM 4.0 Issue

Apr 9, 2008

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):

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.

View 10 Replies View Related

Cobol Data File To SQL Table Conversion

Jul 8, 1999

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....

View 5 Replies View Related

Transact SQL :: Call Cobol From Stored Procedure?

Jun 17, 2015

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 Related

SQL 2012 :: Loading Raw Files Into Database - Datetime Format Conversion

May 23, 2014

I 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.

View 5 Replies View Related

How To Load Several Excel Files In The Same Database (same Structure, But Column Format Different)

May 30, 2008


Here's my need :
In one directory there's several excels file. Theses file have the same structure :
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.



View 1 Replies View Related

What Is The Best Practice To Handle Cobol Redeines In Flat File Data.

Mar 1, 2007

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?



View 2 Replies View Related

SQL 2012 :: Importing Xer Format Files Through Wizard Takes Longer Time?

Aug 9, 2015

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 Related

How To Create UNIX Format Files Using Flat File Connection Manager?

Dec 17, 2007

Hi 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.

View 3 Replies View Related

SSIS SP Fail On Format?

Nov 16, 2007


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?

View 6 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved