Data Conversion

SQL Server Integration Services

Index ‹ SQL Server

- Previous
  - 1
    - Proactive caching notification error I have defined a partition to use proactive caching with a polling query notification. The polling query simply returns the maximum value of a timestamp column from the measure group transaction table. The query parses and runs from the create polling query dialogue box. Have set this to run every 30 seconds. When this is deployed runs the trace on Analysis Services returns this error : OLE DB error: OLE DB or ODBC error: Invalid object name 'fcsUltForecasts'.; 42S02. http://support.microsoft.com/kb/225256 this article suggests can be caused by non dbo owned tables. However this table is owned by dbo (and is the table used when the parition is processed normally) Tag: Data Conversion
  - 2
    - Connecting to SQL Express after Unattended Install Good afternoon, I have written a .NET app that kicks off an unattended install of SQL Server Express Advanced. After the install, the same app tries to connect to SQL Express adn run a script that creates a database plus a few other things. However, the problem I am having is that my app cannot connect. I get the error: "An error has occurrred while establishing a connection to the server. This failure may be casued by the fact that under the default settings SQL Server does not allow remote connections." However, the unattended install goes great. No problems there. I know this because I can Open SSMS, run queries, created DB's, etc. I also know that it is not the remote connections issue because the unattended install is set to allow remote connections (both names pipes and TCP/IP). So, why can't i connect I don't need to reboot after the install do I Please tell me i don't...and please tell me it is something simple I am overlooking. Thanks... Tag: Data Conversion
  - 3
    - Accessing sub totals in a matrix Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column. Tag: Data Conversion
  - 4
    - SQL Server Installed, now what? is there a fee to pay You just need to login with your hotmail Id ! its free of cost ! you need to click the register online and provide the hotmail credentials and take the lab ! there is a pdf manual which you can use and work the exercises in the lab in the virtual server Regards Deepak Tag: Data Conversion
  - 5
    - installtion program dies when installing Native Client next time if you have this kind of a issue please refer setup bootstrap Tag: Data Conversion
  - 6
    - BCP on client machine Hi, What is the best practice to use BCP.exe to load text file from client machine to the SQLSERVER database installed in another server machine , without installing SQLSERVER client tools on the client machine . Any suggestions is appreciated. Thanks in advance. -Anand Tag: Data Conversion
  - 7
    - foodmart database? Where can i find the foodmart sample database for SQL Analysis Services 2005. thanks in advance. Tag: Data Conversion
  - 8
    - SQL Server 2005 JDBC Driver v1.1 and SSL Hi folks, I'm trying to figure out if the SQL Server 2005 JDBC driver supports SSL at all. I've downloaded the driver documentation, looked at more web pages than I care to count, and even went as far as to take a stab at guessing at different driver property names in the hopes I might discover some undocumented feature. Each time I try, I come up dry. The following exception shows up in my JBoss log: 2007-01-03 12:13:36,812 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: The SQL Server login requires an SSL connection.) I know I have things working properly on the SQL Server side, as both osql and Query Analyzer work fine, and packet sniffing yields no plain text information. I've also tried swapping in the open source JTDS driver, and once again, sniffing shows that the data between the two machines in encrypted. Does anyone know if I'm just missing something completely obvious, or can someone state with absolute certainty that the driver does not support SSL Any ideas on how to find out if there are plans to support this feature and what the timeline might be Thanks in advance. Trev. Tag: Data Conversion
  - 9
    - Loading excel file(.xls) into sqlserver (ASP.net web application)(ASP.net framework 1.0) i think i should explain my project... i am doing a web application... Scenario: User goes into the upload page. User selects file to upload through the html control "File field" user press upload button... the upload button should trigger the codes to load the selected .xls file to the database thanks Tag: Data Conversion
  - 10
    - compact sqlce database :-( excuse me, SQLSerer Management Studio must be full, not only Express !!! This is very big download, so it may dissappoint you. BTW, best is to do small project itself and use code :-) Petr Tag: Data Conversion
  - 11
    - Database design - best practices Does anyone know the location of an offical (Microsoft if possible) document that shows the best practices for databse design For instance what to call tables now schema's exist Should it be Countries LookCountry Geography.Country Geography.Countries What about Views and Stored procedures prefix with VW_ sp_ or some alternative Thanks Tag: Data Conversion
  - 12
    - How do can I do this? Hi, thanks to all but here is the solution: SELECT * FROM ( SELECT * FROM tbl ) AS t3 EXCEPT SELECT * FROM ( SELECT t1 .* FROM tbl AS t1 INNER JOIN tbl AS t2 on t1 . colA = t2 . colB AND t1 . colB = t2 . colA ) AS t4 better to know the new operators... :) Tag: Data Conversion
  - 13
    - OLE DB Source to Flat File Destintation using Fixed Width Columns - determining source column width Hi, I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files. However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically. Is there a way to programatically determine the width of a column from the source table I can not seem to find any kind of function or member that stores this information or allows me to retrieve it. I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one. Tag: Data Conversion
  - 14
    - date picker format In IE but I think you said you already checked this: IE: tools - options - general - [languages...] - There may be something else to check, if the above doesn't work let me know and I'll try and find out Tag: Data Conversion
  - 15
    - Data disappearing during Run time Hi J_Dude, Sorry, could interpret a type in your last response... J_Dude2003 wrote: however if I close the application and re open it the data that I'd previously saved is nw there Is that "nw" supposed to be now or not Could you also clarify a couple points Are you using a User Instance (i.e. your database is embedded in your application project) or a connection to the main instance with an attached database Is this problem happening when you're debugging your application or when running a deployed copy Thanks, MIke Tag: Data Conversion
  - 16
    - Old problem, but... Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. I didn't use SQL Express before, now I am trying to use it. I thinkk it is convinient if you just want to copy db files to user's machine. like this <add name="schsecurity" connectionString="Data Source=.\SQLExpress;Integrated Security=true;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" /> as long as user has sql express installed, he should be able to use the db once he copy the file aspnetdb.mdf over. but when I tried it, I got Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. it looks simple, SQL express can not verify user 'NT AUTHORITY\NETWORK SERVICE' so I connect SQL express with SQL server management studio, and add the user into login of the SQL express and set the user as sysadmin. But I still get the error. Any idea thanks! Tag: Data Conversion
- Next
  - 1
    - Results from Aggregate() are wrong The measure I am using is just a simple base measure, not a calculation. CurrentMember doesn't become a concern in this example unless the CurrentMember of [Product].[Product Indexing] is being thrown off for some internal reason. The Product Species hierarchy is unnatural with two attributes. The Product Indexing hierarchy is natural with two different attributes. As I understand this, the slicer (multiselect on the second level of Product Species) is somehow throwing off the Product Indexing hierarchy which is used on rows. Attributes in Product Species are not related to attributes in Product Indexing - unless you go all the way back to the dimension key attribute which is not present in either hierarchy. I've spent quite a lot of time attempting to reproduce this in AdventureWorks but am not able to since I can't find a meaningful pattern behind the problem in my own cubes. I will open a support case and see if I can get somebody to look behind the curtain. Thanks, David Tag: Data Conversion
  - 2
    - Inconsistent Linked Server Query Results Hello, I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query; SELECT * FROM OPENQUERY ( Charlie_file , 'SELECT * FROM [Feb$]' ) Sometimes, on subsequent runs of the above query, I get the following message; Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file". There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span Thank you for your help! cdun2 Tag: Data Conversion
  - 3
    - Can't get Dynamic Security working ... My filter expression in the AllowedSet is different for each level of the hierarchy. For DDO, it's: FILTER([DynManager].[DDO].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("DDO Access List"), "JJester")) For ROM, it's: FILTER([DynManager].[ROM].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("ROM Access List"), "JJester")) For RVP it's: FILTER([DynManager].[RVP].Members, INSTR([DynManager].[DynManager].CurrentMember.Properties("RVP Access List"), "JJester")) and on and on .... Do you have any idea why my test query would work in SQL Server Management Studio, but then I get the "more than one hierarchy" error when I try to apply the same FILTER expression in the AllowedSet Tag: Data Conversion
  - 4
    - Sql Query to create nested XML Sitemap Hi Kent, I had gone through that post earlier and I think that it is different from what I want to achieve. The database contains all the details for a sitemap (such as Url, Title, Description and Roles) and the XML file which I have to generate using sql query is the Site Map file which is used in asp.net to implement the site navigation features on a website. Hope that helps!! Tag: Data Conversion
  - 5
    - How Extract the data of TFINF when doing text mining? Thanks How Extract the data of TFINF when doing text mining Thanks Tag: Data Conversion
  - 6
    - Passing SELECT ALL from URL OK, Here is the code for the 2 functions. one is SQL the other is CLR. You do not have to use CLR, there are multiple other ways to do this. See http://www.sqlservercentral.com/forums/shwmessage.aspx forumid=399&messageid=386759 Also, note that the CLR I show here is limited to 4000 chars and miss a build-in LTRIM... Count chars Code Snippet CREATE FUNCTION dbo . fn_countchar ( @source varchar ( max ), @charval varchar ( 255 )) returns int as BEGIN DECLARE @len int , @icount int , @count int SET @len = len ( @source ) SET @icount = 1 SET @count = 0 WHILE @icount <= @len BEGIN IF substring ( @source , @icount , 1 ) = @charval SET @count = @count + 1 SET @icount = @icount + 1 END RETURN @count end Split multi-param Code Snippet using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server. SqlFunction (IsDeterministic = true , IsPrecise = true , TableDefinition = "SQLStr nvarchar(4000)" , FillRowMethodName = "FillSplitCommaDelimitedStringToStr" )] public static System.Collections. IEnumerable clrfn_SplitCommaDelimitedString( SqlString str) { string x = str.Value; if (! string .IsNullOrEmpty(x)) { return x.Split( ',' ); } else { return null ; } } private static void FillSplitCommaDelimitedStringToStr( object obj, out SqlString str) { if (obj != null ) str = ( String )(obj); else str = String .Empty; } }; Tag: Data Conversion
  - 7
    - configure sql xml support IIS Hope someone can help me...anything would be appreciated. I am using sql server 2000...When I open the Configure sql xml support IIS tool to create a Virtual Directory the only thing that shows up is a computer icon and a IIS Virtual Directory Management for SQL Server. I can't expand ... there isn't a default web site. The other tool called Enterprise Manager works fine with XP. Matter of fact all of the other tools work except for the Virtual Directory Manager. Tag: Data Conversion
  - 8
    - Clone new database from existing All I got was some meaningless generic "operation failed" type of error, plus, the odd behavior I described above when sanitizing (running update queries against) the cloned database. I did try restoring the source db and specifying clone db as target, but was prevented from doing so with a message to the effect of "target database not in dataset so not allowable." If somebody wouldn't mind trying to clone a db from the same server through Sql Express Manager, and let me know the results, I'd feel better about trying it again. When I tried it, I discovered I was simultaneously sanitizing both databases. Rick Tag: Data Conversion
  - 9
    - How to restrict a custom component to be used only in the event handler tab? UITypeEditor and usage is fully documented as part of the .Net framework. Even if you have not installed the .Net help, the MSDN site has it all for free. You should read up on the PropertyGrid control and see what options you have. A TypeConverter may do. Try these to start with http://msdn2.microsoft.com/en-us/library/aa302326.aspx http://msdn2.microsoft.com/en-us/library/ayybcxe5(VS.71).aspx http://msdn2.microsoft.com/en-us/library/53c49eck(VS.71).aspx Tag: Data Conversion
  - 10
    - catch me if you can / bug in the "bug"? 1. Well actually it does, it states that compilation errors are not caught at all. Like "SELEKT *" (which has a typo and a incomplete syntax) 2. Well, its documented :-) Hey, wait a minute, this is a feature :-) 3.Well it won��t as it would break users code. Tag: Data Conversion
  - 11
    - Parameters in an OLE DB Command Hello! Is there a way to use an OLE DB Command transform to execute the following SQL query "Select sum(length(" + @[User::varDBName] + ":informix.cyh_t.u_obj)) from " + @[User::varDBName] + ":informix.cyh_t, " + @[User::varDBName] + ":informix.cch_t where " + @[User::varDBName] + ":informix.cch_t.d_obj = {D '" + @[User::strQueryDate] + "'} and " + @[User::varDBName] + ":informix.cch_t.n_objid = " + @[User::varDBName] + ":informix.cyh_t.n_objid" I get a DB number from an OLE DB Source, do a lookup to get the DB name and then I want to use the OLE DB command to get a result from the query but I can't seem to get this to work. Am I using the wrong transform Thanks. Tag: Data Conversion
  - 12
    - How to set the current connectionString user as a defualt value? Hi, I'm using username & password in my connectionString to connect my program (C#.net) with Sql Server 2005.. I put in defualt value in one feild: user_name() it replace the user: dbo, not the user which I used in my connectionString . so.. how to get this user I tried to put: currnet_user but I get the same result, " dbo " not the user in my connectionString .. Tag: Data Conversion
  - 13
    - Generating letters in SSRS Hi. I'm looking for tutorials on how to use SSRS 2005 to generate letters in Word or PDF format from a database. We need to send letters with our company logo and some boilerplate text to a set of companies based on a SQL Server query. So the letter will look something like this, with the red areas repeating from the dataset, [logo goes here] 123 Any St. AnyCity, CA 90210 [today's date] Dear [name from database] , Some boilerplate text goes here. Your Account [account number from database] is past due. Sincerely, [scanned signature goes here] Somebody Tag: Data Conversion
  - 14
    - Can I retrieve a result set from a sp into a variable within a Execute SQL Task? What *exactly* do you have entered in the SQLStatement box Tag: Data Conversion
  - 15
    - Creating Stored Procedure in SQLServer2005 Are you getting the point to where you see something like this: Code Snippet -- ================================================ -- Template generated from Template Explorer using: -- Create Procedure (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the procedure. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> -- Add the parameters for the stored procedure here <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0> AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2> END GO Tag: Data Conversion
  - 16
    - Help with modifying a data source's query at runtime Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call. The error is Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B I pieced together this code from the examples in the online books, but I am not sure what to do. ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.SqlServer.Dts.Pipeline Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Public Class ScriptMain Public Sub Main() ' Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application() Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _ app.LoadPackage( "c:\systime\ExcelOut\ExcelOut\ExcelOutDo.dtsx" , Nothing ) Dim pkgVars As Variables = package.Variables Dim gsVar As Variable = pkgVars( "User::gsExcelFile" ) Dim currVars As Variables = Dts.Variables Console.WriteLine(Dts.Variables( "User::gsExcelFile" ).Value) gsVar.Value = Dts.Variables( "User::gsExcelFile" ).Value pkgVars( "User::gsQuery" ).Value = Dts.Variables( "User::gsQuery" ).Value pkgVars( "User::gsCreateTable" ).Value = Dts.Variables( "User::gsCreateTable" ).Value Dim e As Executable = package.Executables( "ExcelOutTask" ) Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _ CType (e, Microsoft.SqlServer.Dts.Runtime.TaskHost) Dim dataFlowTask As MainPipe = CType (thMainPipe.InnerObject, MainPipe) ' Get the source component. Dim SourceComponent As IDTSComponentMetaData90 = _ dataFlowTask.ComponentMetaDataCollection( "Local Source" ) Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate() srcDesignTime.ProvideComponentProperties() ' Reinitialize the metadata. srcDesignTime.AcquireConnections(vbNull) srcDesignTime.ReinitializeMetaData() srcDesignTime.ReleaseConnections() ' Get the destination component. Dim destination As IDTSComponentMetaData90 = _ dataFlowTask.ComponentMetaDataCollection( "Excel Destination" ) Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate() destDesignTime.ProvideComponentProperties() ' Create the path. dataFlowTask.PathCollection.RemoveAll() Dim path As IDTSPath90 = dataFlowTask.PathCollection.New() path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _ destination.InputCollection(0)) 'Console.WriteLine(dataFlowTask.PathCollection.Count) Dim ret As DTSExecResult ret = package.Execute() Console.WriteLine(ret.ToString) Dts.TaskResult = Dts.Results.Success End Sub End Class Tag: Data Conversion

slash85

Hi,

I have a flat file with over 300 fields and need to do a data conversion before SQL final destination, most fields are DT_STRING, is there an easier way of converting the data without having to manualy do the 300 fields.

I have all the data types i want STR converting to in a file.

Any info would be great,

Cheers,

Slash.

Re: Data Conversion

Phil Brammer

If they are strings, what are you converting to in SQL Server

Re: Data Conversion

PedroCGD

Re: Data Conversion

slash85

Hi,

Lots of different data types the destination table fields are mailny int, bigint varchar, date and decimal

Thanks,

Dave.

Re: Data Conversion

PedroCGD

Its a lot of fields 300... but doing something flexible as you described I dont know without using code...

In your case I would convert the data directly in the SQL query using CONVERT or using the convertion transform of SSIS...

regards!

Re: Data Conversion

Phil Brammer

It's going to be manual somehow... Either you configure the flat file source with the correct data types, or you add a derived column/data conversion component, or you stage the data into SQL Server and write a SQL statement to convert the view.

Re: Data Conversion

slash85

Hi,

I think i've managed to do it, i manualy went through the import using the SQL wizzard and at the final stage saved to an SSIS package copied the data flow ammened it to fit into my project and its running now.

Thanks,

Slash.