Integration Services :: Progress Database As A Source
Aug 29, 2005
I'm having trouble using a Progress database as a source. I have an OpenLink driver installed and a System DSN set up. I can successfully test the connection. I added this DSN to the connection manager and added it to a DataReader Source. I then added the SQLCommand property. I was able to map columns and such, so I believe the SQLCommand was successfully parsed. However, when I try to save the DataReader Source, I get an error:
Error at Data Flow Task [DataReader Source [2266]]: System.Data.Odbc.OdbcException: ERROR [HY010] [OpenLink][ODBC][Driver]Function sequence error at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcDataReader.NextResult() at System.Data.Odbc.OdbcDataReader.Close() at Microsoft.SqlServer.Dts.Pipleline.DataReaderSourceAdapter.ReinitializeMetaData() at Microsoft.SqlServer.Dts.Pipleline.ManagedComponentHost.HostReinitialieMetaData(IDTSManagedComponentWrapper90 wrapper)
I'm using following command to populate my OLE DB Source. I have two of those in each Data Flow in my package. One of the OLE DB Sources points to my source database the other to the destination. In order to limit the number of rows I use the WHERE clause below. The [EnergyMiserFSRLive] being the the name of the source database. The Connection manager points to the destination database.
I would like an elegant way to replace [EnergyMiserFSRLive] with a parameter which I can reuse in each of my many data flows rather than use this hard coded value [EnergyMiserFSRLive].In particular I'm after the syntax of the below query that uses the parameter for [EnergyMiserFSRLive].
SELECT [SitesId] ,[SiteName] ,[FilePrefix] ,[City] ,[StateProv] ,[Country] FROM [Sites] WHERE SitesId >= (SELECT MIN(SitesId) FROM [DBNameFSRLive].[dbo].[Sites]) ORDER BY [SitesId]
I need to pull in data from a progress database into a table in SQL server 2005. I have an ODBC conncetion on the server where I am working.
I am new to SSIS but have used DTS. I need to succsefully pull the data into a table in SQL server 2005 from this progress database.
I have setup my ODBC connection but I cannot pull the data using this connection. The connection test succeeded for the connection manager. When I attempt to use the datasource reader and point to the ODBC Connection Manager entry I recieve Cannot acquire a managed connection from the run time connection manager
I have a got a package with source as sql table which has got 50 columns. We are using only 10 columns out of this. Recently one column name has changed and thus throws error invalid mapping. When I open the source to do the changes noticed that all the colums are prselected now and also the datatypes got changed to default ( I had changed the datatypes as per my requirement while i developed). So now I had to select required columns from source and redo the datatype changes in advanced editor.Is there any option which doesnt disturb this settings and we just need to correct the mapping alone.
Please let me know whether we can use Teradata as Source in SSIS (Target is SQL Server 2005). ie. Do we have oledb driver for NCR Teradata to connect to it. Our SSIS will be hosted in 64 bit SQL, but for development we use 32 bit.
I've got a problem to retrieve data from a Xml Source. Basically, I call a method from a Web Service which gives me a Xml file.
The problem is that the XML structure is not really good. But we can't touch it.
Here is the Xml File :
Code Snippet
<?xml version="1.0" encoding="utf-16"?> <ArrayOfWSTargetVO xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <WSTargetVO> <ProjectId> <Value>131</Value> </ProjectId> <Id> <Value>Toto</Value> </Id> <Name> <Value>bateau</Value> </Name> </WSTargetVO> <WSTargetVO> <ProjectId> <Value>131</Value> </ProjectId> <Id> <Value>Tata</Value> </Id> <Name> <Value>F35</Value> </Name> </WSTargetVO> ... </ArrayOfWSTargetVO> As you can see, for each WSTargetVO, we have a projectid, an id and a name. But the value is not directly put into these nodes but in a new one : <value>
That causes my problem because here is the xsd file generated by visual studio :
Code Snippet
<?xml version="1.0"?> <xsd:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsd="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified"> <xs:element name="ArrayOfWSTargetVO"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" maxOccurs="unbounded" name="WSTargetVO"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="ProjectId"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="Value" type="xs:unsignedByte" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="Id"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="Value" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="Name"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="Value" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xsd:schema> And when I try to use the outpul results from the Xml file, I can't see how I can get a datatable with three columns corresponding to projectid, id and name.
Integration Services only asks me to choose between WSTargetVO or ProjectID or Id or Name and give me the <value> value.
I don't know if it is possible to modifiy the contents of the XmlFile or something else using XPath.
Of course, if I try to modifiy the XSD file and delete the value node to have a simple structure, I see my three columns but i can't get any data.
I'm aware that the XML file is pretty bad but it is impossible for me to change it.
If somebody has an idea, I would be happy to hear it :-)
I have a Data Flow Task. I have one "OLE DB Source" which gets my data from a SQL Server Database. I have a second "OLE DB Source" which uses DATEADD to derive a date qualifier that I would like to use as a date qualifier in my subsequent Excel spreadsheet...opting to use SQL Server and DATEADD rather than messing around with VB syntax to get the previous week date qualifier.I am trying to connect the flow from one OLE DB Source to the next OLE DB Source and get the error..Component OLE DB Source has no inputs, or all of its inputs are already connected to other outputs. You may be able to edit the component to add new inputs to it.Can't I connect two completely different and independent SQL Server queries using "OLE DB Source" within my Data Flow?
Is there any way to store my derived date from my second "OLE DB Source" to a variable so that I cana then use that as my date qualifier within my Excel destination?
I have two servers (TESTSERVER, PRODSERVER). On the TESTSERVER i have a package that i'm using for data load from a source database on a SOURCESERVER. Everything works great until this point. Now from the PRODSERVER in the SSIS package i'm trying to create a connection in the connection manager to the SOURCESERVER and i'm getting the following error
Test connection failed because of an error in initializing provider. Login timeout expired.A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
I have one small requirement.. I want to load the different types of files(.txt, .csv, .tsv, .xlsx).
Using one forearch loop container how can I load the files to database and I shouldn't use the script task to split the filenames. Is there any other way to load all the files using forearch loop container, exesql task..
I have ssis package that pull data from SAP (Using ADO.net connection) to SQL server every night but i have noticed that all data from source is not getting pulled by package . package losing some amount of row.
I am working on 1 POC project.I have 2 customer having source file in txt format, but the column sequence of both customer are diffrent.Number of columns in all files are like below.
CustA
ID NAME AGE 1 VIPIN 29
CustB
ID AGE NAME 2 29 jayesh
As per source file you can see that CustA have column sequence ID,NAME,AGE and CustB Have ID,AGE,NAME sequence .I have target table #Temp with ID,NAME,AGE sequence.Like that I have many files from both customer, I have to load in ID,NAME,AGE sequence from all source file to target table.How can we change the sequence of source column before loading to target table.
I have created a SSIS Package which does the incremental update using CDC Controls.
The design is similar to any standard CDC incremental package.
It has a CDC Start which sets the Mark Processing Range, a data flow and a Mark Processed Range.
The issue that i'm facing is that the CDC Source control time-out but i can still see rows moving from CDC Source to Splitter and target table. After the rows are transferred, the Data Flow task Fails which leads to package failure.
This results in Mark Processed Range not being executed.
So my query is
1. Why is CDC Source being time-out?
2. What can i do so that all three i.e Mark Processing Range, data flow and Mark Processed Range execute successfully or nothing does.
My package is having .csv file as a source and I kept OLEDB destination to load it.
Stored the .csv file in a shared folder and the exact path is given in Enumerator configuration of the foreach loop container. When I execute my package, it is giving the warning as below:
It is saying that file is not there in the specified path and directory is empty. I am running the SSIS package from TFS. I am sure that I have read and write access for the shared folder for my userID. Is there any access there to pick up this file from path.
I have installed the SharePoint adapters from codeplex and they show OK in SSIS 2008R2. But in SSIS 2012, I can't find them and their is no SSIS component tab to pick it and add it to the toolbox.
I want to use the excel sheets located on a share point site as source, for this i downloaded the adapter from the following link URL....I got the connection manager to work, but when coming to load the excel files to the destination location its not doing what is intended. For example i want the destination location my local drive for the excel files, how can i do that which destination should i use?
I have a scenario where we have to handle dynamically changing source columns.
For example , some times in the source files the number of columns will be increased or decreased, new columns can be added in the middle or in the end of the source file.
I want to design an SSIS package that loads data from files into SQL Server and I want to automate the process. My major issue is that the source file doesnt come in the same format. Some times I comes in either .csv , .xls , .txt or even .rpt file format. Is there a way I can write a code that checks through my folder and based on the available format on the folder it loads the value in ssis.
I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
I am able to collect data from Progress DB, using ODBC Connectivity. The problem I am facing is, i have to iterate thru multiple servers. How do i configure ODBC source dynamically. It creates problem. Using expression, i tried to set the connectionstring dynamically, but it fails.
I have a package from SQL Server 2008 R2, that loads data from .xlsx file to database table.There are total 15 columns and 14000 rows in the .xlsx. The package runs fine in BIDS. But the same package in SQL Agent fails with error "omponent "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
When I tried to run the package by deleting the half of the records for first 7000 rows it ran successfully in agent. Then the second half (last 7000 rows) also succeed from agent job. So, there is no issue with the data/datatypes.The agent job is able to run with record upyo 11000 rows in .xlsx. When I am running for 12000 rows it is failing.Is there any problem with the number of records in .xlsx or size through SQL Agent?
I am running the package from a Proxy account in sql agent job.
ERROR: Error: Executed as user: PROXY_ID. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 32-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 10:36:09 AM Error: 2015-08-10 10:36:10.87 Code: 0xC0202009 Source: XX Connection manager "Excel Connection Manager 1"
I would like to fire a pre execution event, grab the name of the stored procedure (source of the sql task), insert a record with the name and datetime, and then fire a post event that would update the record with a modified dated.
What is the best way to capture the source value name in the execute sql task.
After designing a SSIS package in Visual Studio 2005 that had two connection manager defined to keep the password. After I deployed the package to a file system. I then Imported the .dtsx file after making a Integration Services connection in Sql Server Management Studio. When I tried to run the package it failed when it tried to make the connection. When I edited the connection manager connection string and added the password and the package ran fine but it does not retain the password!. I need to have this package scheduled to run daily so I need to know how to have the package keep the password in the connection string. I have seen other posts on this issue but not seen a good solution. Could someone point me to the proper MSDN article that would explain how to implement this ? Is it a SQL Server configuration issue or a property in Visual Studio SSIS design time ?
we need download files from FTP location , so we are using script component as source and when we are using the fallowing code// read a file and write out as columns in rows
WebClient myWebClient = new WebClient(); myWebClient.Credentials = new NetworkCredential(Variables.ftpLogin, Variables.ftpPassword);
// Concatenate the domain with the Web resource filename. string myStringWebResource = Variables.fileURL + Variables.fileName;
string s = myWebClient.DownloadString(myStringWebResource); StringReader reader = new StringReader(s);
we are getting the fallowing error The server returned an error: (404) Not Found. are we missing anything in the code.
I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
I have a Connection manager to an Excel workbook. I've set it up as Microsoft.Jet.OLEDB.4.0 Excel 8.0;HDR=NoWhen I create the DataFlow OLE DB source, the Name of Table or View dropdown shows only 3 items:
Resources$ Resources$_ and Volumes$_
My workbook has 2 tabs (Resources and Volumes; Volumes is hidden), two named ranges of the same name (Resources and Volumes) and a bunch of other named ranges.
Why does the drop down only show these three things, and only the first one (the first tab Resources$) actually works.
The other two give a nasty message (0xC02020E8 Microsoft.SqlServer.DTSPipelineWrap)
I need to grab data from teradata(using odbc connection).. i have no issues if its just bunch of joins and wheres conditions.. but now i have a challenge. simple scenario, i have to create volatile table, dump data into this and then grab data from this volatile table. (Don't want to modify the query in such a way i don't have to use this volatile table.. its a pretty big query and i have no choice but create bunch of volatile tables, above scenarios is just mentioned on simple 1 volatile table ).
So i created a proc and trying to pass this string into teradata, not sure if it works.. what options i have.. (I dont have a leisure to create proc in terdata and get it executed when ever i want and then grab data from the table. )
I'm encountering a very peculiar situation when I'm trying to compare source and target data using conditional split. Following is the Data Flow and how I'm trying to achieve this.
Source Data : Col_A (PK) Col_2 1 100 8 500 Target Data : Col_A (PK) Col_2 1 100 3 700 8 500 Look-up Target on Col_A to check for existing records. Now we have four columns in Look-up match output: Col_A, Col_B, Lkp_Col_A (Target Col), Lkp_Col_B (Target Col).
Conditional Split: Compare Col_B with Lkp_Col_B
Update target if there is any change in the existing value of Col_B.When I'm running the package for every record in source, the conditional split fails and even when there is no change in Col_B, some of the records (Not all and quite randomly) get updated with the same value. If I run the package for few records, it works absolutely fine.
We run std 2008 r2. I'm trying out the commandtimeout property of an oledb source. I set it to 30 expecting 30 seconds. if connection and or execution exceed that threshold, will the pkg fail? Either way is there a way I can detect that the threshold was exceeded?
