1.After creating pacakages what i am concerned about is that when i move then to different locations do they work just the same as the had been.
2.About the mapping with in the components like lookups,and the variable with in conditional split or etc....do they get disturbed with any change in the location of the textfiles or anything of this kind of location change.
3.Is is adviceable transforming 5 to 6 text file with in one package in parallel
OLE DB source which calls a stored proc that returns a result set
data conversion
Excel destination I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
What am I missing or doing wrong? Thanks in advance
We have a strange problem occurring in an environment at a remote client that we're looking into. I was wondering if anybody else has experienced something similar.
We have a COM+ transactional component sitting on a machine that connects to a SQL server machine in the same room (ie no firewall between the two as far as I know, but I'll double check that to make sure).
This component was written in Delphi and uses ADO (sqloledb provider) to connect to the SQL Server. This works 99% of the time. However, there are times where we get the following error when trying to open connection (using the Connection ADO interface):
[DBNETLIB][ConnectionRead (WrapperRead()).]General network error. Check your network documentation
or this:
Connection failure
The error codes associated with these were found in oledb.pas :(-2147467259) $8004D01C and (-2147168228) $80004005
When this happens, it seems to last a few seconds (usually around 10) and then everything comes back to normal. I'm just wondering what could make this happen when the SQL Server seems to be responsive when we try to connect to it using other means. The SQL machine runs Win2k3 sp2 for testing purposes.
Since we were not able to reproduce it yet, I wrote a little application trying to connect to a sql server when it is set on pause. When I run it inside a COM+ transaction, I get the same errors as above right away, the third time I'll get an error telling me that SQL is in pause state, If I run the exact same code but inside a simple vbs application (ie no COM+ transaction), I only get the pause error message sometimes after a few more seconds, and none of the other errors above. Could it be that the COM+ transaction is trying to do other things and these fail so it returns another error? If so, how come after a bit I get the proper message?
Anyways, I'm pretty lost as to why this is happening and how we can try to debug it since we have not been able to reproduce it yet. If anybody has any clues, please let me know.
Within a data flow I have a script component that I want to perform some data calls from. I want to execute SQL commands to connections already defined in the package's connection manager.
Is there a way to set my script's connection variable to the connection manager connection? Or, can I use the connection manager connections directly from within the script?
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Is it possible to get information of the mappings availabe in the packages?, like the column mappings in the OLE Destination control, column mappings in the merge join control etc.,. I want the information of source and destination in the mappings(name of the DB, table). I want that to be in some file like excel file. Inform me is there a way to get it?
Is it normal that my scd loses its mappings if I change one of its inputs?
When I talk about mappings, I talk about the first page of the scd wizard. I set everything (all the columns, business keys) and run the wizard until the last step. SCD creates two outputs (Changed and New Rows). All Ok.
If a column I use as input in the scd changes (for example, it is not a conversion column any more but a derived with the same name), my scd loses all the mappings!
It is very annoying since I have to map all of the columns again. Is this a normal behaviour of the scd or I' doing something wrong? Is there a way to 'repair' my mappings without needing to set one column at the time every time?
I have a situation where I want to map a column from a flat file to TWO columns in a table.
However, in the mappings tab, you can only select the "Input Column" once. Once a column has been used, it no longer appears in the drop down list.
I am wondering if there's a way to override this behavior, and if not, what is the best way to handle this type of situation?
I have added an EXECUTE SQL task to update the second column with the inserted column values, but I would like to know if the default mapping behavior can be changed, as it seems so limited.
Is it possible to save column mapping definitions from a Transform Data Task? The practical use is I have four tables with very similar layouts of which 200 or so columns are identical. I have various front and back office applications that require local copies of this data in various formats. It is EXTREMELY tedious to remap all of the columns for each Transform Data Task required on these applications.
Is there a way to store all of the column mapping def's and import them into a new transform data task?
My need is to load data from flat files to SQL tables. All the flat files are different. For convenience flat file name and table name is similar. I have placed a For loop container and in which I used SCRIPT TASK to read the file names from the specified folder and i populated this value to SSIS variable. All my flat file name and destination table name are given using this Script task. For the first run it executes perfectly, but for the second run it fails. The reason is due to the flat file connection manager. The file name is changed since it is taken care by my script, but the columns are not mapped it still have the previous file's columns. I do not know how to solve this.
Please tell me if there is any other way to solve this or i need to refine my process.
Actually i have more than 100 files and so i am trying to do it at one short rather than creating individual packages.
I have SSIS packages with the usual: source, destination, and mappings between the two. How can I export these mappings to, say, Excel / Word for documentation purposes?
hi - I am totally new to SSIS etc and SQL 2005. I have a dts task to recreate in SSIS. I have done most of them and muddled my way through, but this basic problem has got me stuck. When mapping columns from my file to my ole db output table, I want to map one input column onto two output columns, but it will only seem to let me select one destination column for each input? I have tried shift/alt/ctrl etc to try to get it to map to both columns but it wont have it. How do I do it?
Also, somehow my Dataflow Sources tab has gone from the toolbox, and I can't seem to get it back any way - I switched on everything I could see and all components etc, but it is not in there as an option. How do I get it back in the toolbox?
Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2". The Table is like this.
TblEmp1 : Columns EmpName EmpDept EmpjoinDate
TblEmp2 : Columns EName EDate Edept
using integration services (SSIS) i need code(vb.net or c#) to Create a dtsx package so that i can push the flat file content to these 2 tables. And the condition is :
After Executing the package Data loaded in TblEmp1 should be like this
Now, i know that we need to do like this in wizard 1) Create a flat file source component. 2) Create flat file connection and set the properties of flat file (delimeters and other things) 3) Create a Multicast Component. 4) Create a Path between Flat file source and Multicast. 5) Create 2 destination component(each for a table). 6) Create path from multicast to 2 destination components 7) Create a OledbConnection and set table names for 2 destination components.. 7) Now,i have to do mapping for destination1.8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.
I have done it in wizard.I need to do it through code and i know that its not complicated.Please find the attached file with this mail.i have attached a screen shot of how i have done in wizard.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!
I want to retrive the output in a single SQL query.
I had two columns in a table; I used some values of column1 in column2. I want to identify all the column1 vlaues in Left Hand side and column2 values in Right Hand side, with a single query.
example:
MasterTable
ParentRow ChildRow --------- -------- A B C D E F G C H E M G
Output required:
ParentRow ChildRow --------- -------- A B C D E F G D (C mapped tfrom D) H F M D (G mapped from C, and C mapped from D)
To get the above output, I written query using WHILE...[BREAK|CONTINUE] concept until I get @@rowcount become zero.
I am looking for better performace and to get the output in a single query, instead of multiple passes.
Can any one suggest good tools and articles on performance tuning techniques?
Has anyone done this? I can't find anything in the documentation that describes this. The closest I get is to the InnerObject property of the TaskHost class. There is an example of programming a bulk insert task. But I can't find anything on programmatically setting the column mappings (source to dest) of a simple data flow task. Any help is appreciated!
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type. On exiting from the script the variable of type object should contain something like in the following lines: AAAAAAAAAAAAAAAAAAAAAAAAAAAAA BBBBBBBBBBBBBBBBBBBBBBBBBBBBB CCCCCCCCCCCCCCCCCCCCCCCCCCCCC DDDDDDDDDDDDDDDDDDDDDDDDDDDDD €¦€¦€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦€¦. On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion. Is there anyone who have experienced something like this? Could anyone provide any example of that? Thanks in advance!
I have flat file (comma delimited) with 200 columns, and i want to import this to sql table using SSIS package, I create a Flat file source and sql server destination items from tool box. in destination item, i can not see the preview, for some reason, it is not reading the rows from source, Do I have to manually each and every column from source to destination under mapping tabl in destination item.
I am getting the error "The required .svc handler mappings are not installed in IIS" in the Master Data Services Configuration Manager. I am running the configuration tool as administrator on a domain joined machine using a domain user account (which is also a member of the local Administrators group).
My configuration is as follows: Windows 8 SQL Server 2012 RTM + CU 2 (build 11.0.2325).
All required prerequisites should be installed in IIS.I have reviewed this issue:Â URL...
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I am writing a custom dataflow transformation component and I need to get the name of the preceeding component.
I have been trying to find a way to get a reference to the Package object, MainPipe object or IDTSPath90 object (connecting to the IDTSInput90 of my component) from my component because I think from there I can get to the information I want.
I am a bit confused for the model evaluation (lift chart), should we map all the columns for both the mining structure and the case table? I mean for those predictive models, we have a predict column, shouldnt we ignore the mapping of the predictive column between the mining structure and the case table? But it seemes we are not allowed to miss the predictive column mapping between the mining structure and the case table.
Why is that? Could any experts here give me some explanation on that?
Hope my question is clear for your help.
Thanks a lot and I am looking forward to hearing from you shortly.
No idea where this bug crept in from. Have been using SSIS for 1.5 years now without hitting this problem.
I had a script component opening an XML document and parsing it using XPATH. I added some code that uses StreamReader / Streamwriter (closing one stream before starting the other). The code works without issue in my C# app.
And it ran without issue 2-3 times in SSIS. Then suddenly after running my package again, the script component says it completes successfully, yet nothing happens. I set a breakpoint on the first line of code - it never hits it. I add a msgbox as the first line of code - and it never displays.
I then close my package / exit out of ssis ... and then re-open it. When i open my script component, all of my code is GONE. All references that I added are gone.
I tried adding the streamreader/writer process to a dll I created from my c# app ... and added the DLL to the package -- same result.
I can reproduce this on 2 different computers.
Anyone experience this problem ? Any idea how to stop it ? Or debug it ?
Here is a slimmed down code sample of what causes the error :
Public Class ScriptMain Public Sub Main() Try Dim xmlDoc As New XmlDocument xmlDoc.Load("c:ulkasync_86281519_20070628045850225_4.xml") MsgBox("xmlLoaded") --this doesn't display once the package starts "acting up" Catch ex As Exception MsgBox(ex.Message) UpdateXML("c:ulkasync_86281519_20070628045850225_4.xml", ex.Message) End Try Dts.TaskResult = Dts.Results.Success End Sub Private Sub UpdateXML(ByVal fileName As String, ByVal message As String) Try Dim invalidChar As String = message.Trim().Substring(message.Trim().IndexOf("0x"), 4) Dim rd As StreamReader = New StreamReader(fileName) Dim xml As String = rd.ReadToEnd() Xml = Xml.Replace(invalidChar, String.Empty) xml = xml.Replace("", String.Empty) xml = xml.Replace("<![CDATA[<![CDATA[", "<![CDATA[") xml = xml.Replace("]]>]]>", "]]>") MsgBox("replaced") rd.Close() Dim wr As StreamWriter = New StreamWriter(fileName) wr.Write(xml) wr.Close() Dim xdoc As XmlDocument = New XmlDocument() xdoc.Load(fileName) Catch ex As Exception UpdateXML(fileName, ex.Message) End Try End Sub End Class
Hi there, Here we have got a asp.net application that was developed when database was sitting on SQL server 6.5. Now client has moved all of their databases to SQL server 2000. When the database was on 6.5 the previous development team has used oledb connections all over. As the databases have been moved to SQL server 2000 now i am in process of changing the database connection part. As part of the process i have a login authorization code. Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean Dim conn As IDbConnection = GetConnection() Try Dim cmd As IDbCommand = conn.CreateCommand() Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation" 'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql cmd.CommandType = CommandType.StoredProcedure NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username) NorisHelpers.DBHelpers.AddParam(cmd, "@password", password) conn.Open() 'Get string for return values Dim ReturnValue As String = cmd.ExecuteScalar.ToString 'Split string into array Dim Values() As String = ReturnValue.Split(";~".ToCharArray) 'If the return code is CONTINUE, all is well. Otherwise, collect the 'reason why the result failed and let the user know If Values(0) = "CONTINUE" Then Return True Else results.Result = Values(0) 'Make sure there is a message being returned If Values.Length > 1 Then results.Message = Values(2) End If Return False End If Catch ex As Exception Throw ex Finally If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then conn.Close() End If End Try End Function ''' ----------------------------------------------------------------------------- ''' <summary> ''' Getting the Connection from the config file ''' </summary> ''' <returns>A connection object</returns> ''' <remarks> ''' This is the same for all of the data classes. ''' Reads a specific connection string from the web.config file for the service, creates a connection object and returns it as an IDbConnection. ''' </remarks> ''' ----------------------------------------------------------------------------- Private Function GetConnection() As IDbConnection 'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON) Return conn End Function in the above GetConnection() method i have commented out the .net dataprovider for oledb and changed it to .net dataprovider for SQLconnection. this function works fine. But in the authenticate method above at the line Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error. Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate. If i comment out the Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection and uncomment the .net oledb provider, Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection then it works fine. I also have changed the webconfig file as below. <!--<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;Auto Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";' />--> <add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
I get the following error when I try to navigate to a report/model/data source, stored in a Sharepoint Document Library using a Sharepoint URL based on an Extranet or Intranet zone - but it works OK using the url for the Default zone:
System.Web.Services.Protocols.SoapException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used. ---> Microsoft.ReportingServices.Diagnostics.Utilities.SecurityZoneNotSupportedException: The specified path refers to a SharePoint zone that is not supported. The default zone path must be used.
Is this a limitation of the SQLRS-WSS3 integration or is there a workaround?
Just a quick question about connection management. My application willnever need more than 1 or 2 connections about at any given time. Also, I donot expect many users to be connected at any given time. For efficiency, Iwould like to keep connections alive throughout the lifetime of the objectsrequiring them, rather than opening a new connection, executing code andthen closing it again. What is the most efficient way of doing this?Should I perform the open/close or just one open when I create the objectand a close when I dispose of it?
Hi I have some simple questions of for MS SQL. Say I have a database with a table Called Company. In this table I have Employee's with these columns(EmployeeID<PK>, FirstName, LastName) Now I am inserting some data like this.EmployeeID FirstName, LastName------------------------------------------------------- 1 Bob Smith2 Joe Mitter Now Say I have this situation(this will look weird for this example but for another examples and the stuff I am working on it make sense). Say I have a new Employee Called Jessica White. Jessica must be the second record. So I need to insert it between the 1st and 2nd record. Like I said I am very noob at doing this stuff so I am not sure how I would do it. I first was going to my table and then went to modify. This brought up a blank grid with all my column names. I then started to add all my data in. I found out later to make my life easier I need to enter in certain spots null rows in it to help with formating(otherwise I would have had to figure out a way to do make null rows with c# code). I then went back to this grid and I tried to add a record before the data just like you if you used excel. I quickly found out you can not do that. This resulted me having to recreate the table and reput the all the data back in(this told me I was doing something very very wrong). I was then thinking of writing it with a query and I been playing around with it and still running into problems like say I had a table called test with a coloum called id<PK> and testss(yes bad names just playing around though).So if I did something like this:INSERT INTO testVALUES(2)INSERT INTO testVALUES(1)SELECT * FROM testNow I am trying to figure out how to insert something between rows 1 and 2. Would I have to drop the table or delete all the data and then run a saved copy of my script with the added change or what?My second question is what is a good site that has examples of all the commands(I don't want to read a book on it I have had a oracle sql course and well it was just boring and I did not learn too much.). I think the best way for me to learn is just continue doing my site and picking it up on the file and when I got a problem read about it and ask on the forums. Thanks
Very strange event. Installed new machine into NT 4 network, which has a PDC and a BDC. We have copies of all SQL databases on both the PDC and the BDC. I installed SQL 7 on new workstation. Throught Control Panel/ODBC connections I can reach the SQL files on bith the PDC and BDC. Howver when I try to use Enterprise Manager, I can connect to the tables on the PDC, but not the BDC. SQL lists both machines in the server group, but only allows me to access tables on PDC! Here is the Event information that I recived on the BDC:
The computer IVR1 tried to connect to the server NTBACKUPSERVER using the trust relationship established by the MASSCOM domain. However, the computer lost the correct security identifier (SID) when the domain was reconfigured.
HI all, I have a problem here. I am having two computers both are loaded windows NT 4.0 AND SERVICE PACK 4 AND ALSO I INSTALLED SQL SERVER 7.0. Now i want to connect those two servers , so pls anyone suggest me how i have to do.I connected both servers thru HUB. Pls suggest me.. thank u..
