I have a column which uses a DEFAULT as GETDATE in one of my tables. When I execute a DTS package to insert data into it, the column values are all the same, but if I use SSIS, the dates differ slightly (by a few ticks after several rows, but not a consistent amount of rows).
Is there an explanation for this difference, and how can I correct this problem?
Good afternoon,
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE TABLE [dbo].[TestTable](
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[SSN] [nvarchar](15) NULL,
[IsGeek] [bit] NOT NULL CONSTRAINT [DF_TestTable_IsGeek] DEFAULT ((1))
) ON [PRIMARY]
I then created the following stored procedure:
CREATE PROCEDURE TestTable_Insert
@FirstName nvarchar(50),
@LastName nvarchar(50),
@SSN nvarchar(15),
@geek bit = NULL
AS
BEGIN
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, @geek)
END
GO
and executed it as follows (without passing the @geek parameter value)
EXEC TestTable_Insert 'scott', 'klein', '555-55-5555'
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek)
VALUEs (@FirstName, @LastName, @SSN, ISNULL(@geek, DEFAULT))
Does this make sense?
We receive thousands of files every week from various clients and we attempt to clean the columns using the same technique over and over so the data is consistent. The problem is I dont see a way to reuse complex column transformations in different packages. I would hate to have to go change every package if we change the rules for cleaning a column.
So #1: Can you create some kind of script or .net function that cleans a column and reuse it in multiple packages (or even in the same package)?
#2: Is it possible to call functions from the Derived Column expression builder?
Thanks!
Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spread sheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located
C:Program FilesMicrosoft SQL Server100DTSMappingFiles
Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10?
Hi all,
Can anyone tell me some links which can gimme good insight on the SSIS transfoemations.
Thanks,
Praveen kumar Dayanithi
How do i know whats the default values on which table and which column and whats the default value
It should be like this
Table columnconstraint_name value
empemp_idDF_emp_emp_no 1
empjoin_dateDF_emp_cur_date getdate()
How can i get these results on all the tables in my DB.
Thanks.
I have a 64-bit box with SQL 2000 and 2005 installed on it. At the command prompt, typing BCP -v gives me the 8.0 version, not 9.0. I really want the 9.0 version as the default. Obviously typing
"C:Program FilesMicrosoft SQL Server90 oolsinncp" gives me the 9.0 version.
My PATH variable looks like this:
Path=C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem;C:Program FilesAT
I TechnologiesATI Control Panel;C:Program FilesIntelDMIX;C:Program Files (
x86)Microsoft SQL Server80ToolsBINN;C:Program Files (x86)Microsoft SQL Ser
ver80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program
FilesMicrosoft SQL Server90Toolsinn;C:Program Files (x86)Microsoft SQL
Server90Toolsinn;C:Program Files (x86)Microsoft SQL Server90DTSBinn;C
:Program Files (x86)Microsoft SQL Server90ToolsBinnVSShellCommon7IDE;C:
Program Files (x86)Microsoft Visual Studio 8Common7IDEPrivateAssemblies
Do I dare change the order so it looks in the 9.0 directories first? Or is this by design?
thx
Right, so I'm currently trudging through the SQL video tutorials and such, so it may be that I get to this sooner or later, but as I'm under a deadline, I thought I'd post this question beforehand so I can use that info with what I'm learning now. Here's my situation: I have a ASP.NET 2.0 site in which I currently use XML files to display the text on the page, and I transform that text using an XSL stylesheet. I want to move that data to a database, but I'm not sure what is the best way to do that. Basically what I'm most concerned with is storing the main text (paragraphs with embedded hyperlinks). Currently, I can get the XSL to pick out the links and transform them from simply XML data to live links when they display on the page, but would I be able to do the same if I were pulling these paragraphs out of a database? Or should I just store the XML data in the database, and still pull that out so I can transform it appropriately with the XSL sheet I already have? (For that matter, can I dynamically write XML content to a database? Or am I just better off keeping my XML files?) What's the best approach for something like this?Thanks for the help!
View 4 Replies View RelatedI want to change the rows of the following table to columns to avoid repeatability:
Manufacturer
AOpen
Model
s661FXm s661FXm Intel P4
System Type
Motherboard
Standard Memory
N/A
Maximum Memory
2 GB
Sockets
2
Slots/Banks
2
Manufacturer
HP/COMPAQ
Model
Presario SR1917FR AMD Athlon 64 X2 3.06 GHz
System Type
Desktop
Standard Memory
1024 (1024MB x1 Removable)
Maximum Memory
4 GB
Sockets
4
Slots/Banks
4
Can this be done using Pivot Transformations? If yes then which column will have pivotusage and which will have pivotkeyvalue. I am getting a little confused here.
Please help!!!
Thanks
I am importing four large flat files and have some formatting issues with dates. I've figured out how to process the dates within a package exactly the way I desire. Unfortunately, the process has several steps that I don't want to have to repeat for each date field. Is it possible to call a reusable sequence of transformations that take parameters and have a return value? Is there any other way to achieve similiar results?
View 1 Replies View RelatedHi all,
In my DataFlow i set the "OLEDB Source" which is a table in my Extract Server and need to do some transformations and stage the table which will be a Dimension in the staging DB,
Q1-Now i need only 3 columns from the Source table, which transformation do i need to use to just extract the the 3 columns?
Q2- Two Columns of 3,which i will need to transform as it is-no changes at all and One of the column which has values like "BOSTON...."
(I have a vague idea of what i need to do,need something solid suggestions/advices to kickoff,plan is to use this city column with a Replace function (as one of the forum member's Spirit1 adviced..thanks..!!))to take out the dots and then need to write a condition if BOSTON then Assign Code "BOS" which will be City_Code and this "City_Code" will have to be looked in City_Dimension to get the "City_Key_Number" for "Boston" and lastly the City_Code and City Key Number both have to be transformed to the destination Dimension.
Any ideas /suggestions will be appreciated.
Thanks in advance...!!
ravi
Hi
I have a dts that is creating a table with not a fixed number of columns. The number of colums depend on a couple of factors based on the data that I'm pulling from other tables.
After some processing I need to dump all the data in the "dynamic" table into an excel doc. My problem is with the transformations within the transform data task. I don't know how many fields I will have in my table and this needs to be mapped to columns within the excel doc. Is it possible to programmatically define the transformations within an activeX script or what can I do.
Thanks
Johnnie
The following statement is from Microsoft documentation:
If you use the ExclusionGroup property to specify that rows should only go to one or another of a group of outputs, as in the Conditional Split transformation, you must call the DirectRow method to select the appropriate destination for each row. When you have an error output, you must call DirectErrorRow to send rows with problems to the error output instead of the default output.
I have a question about this because I have never used the "ExclusionGroup" property. For example, I have a script component where I specify 4 separate outputs, because I am sending different groups of rows to each output. I accomplish this programmatically using a lot of conditionals and it works fine.
I did not have to use the "ExclusionGroup" property to do this. So I'm not sure why I would ever need this, or to use DirectRow? I'm trying to understand this better, because maybe I feel like I'm not understanding the DirectRow, or how/when to use it.
Thanks
Hi!
I´m experiencing problems with datepart(weekday,...) function. In particular with week 54 of 2000... (by the way, don´t make a bet about that, because Jan 1st. was saturday (last day of week) and year 2000 was a leap year!!!, so Dec 31 became the one and only case of 54 weeks... in our calendar).
Is there a way I can fix sunday as first day of week, instead using SET DATEFIRST in every procedure? (because "somebody" puts MONDAY as a default in spanish)
Thanx in advance,
César.
I have run the following script to create a default on a table:
alter table TableXYY
add default 0 for ColumnX
Now, how do I delete this default (without re-creating the table)??
Thanks
I'm having some problems when trying to import an external table into my SQL Server 7.0 DB. When date fields are being created the datatype is being set to smalldatetime which is causing errors because some of the data in the source table is using dates proir to 1900.
Is there a way I can set the datatype to be set as datetime instead of smalldatetime?
I have a stored procedure that has @BeginDate and @EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @BeginDate and GetDate()+10 for the @EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'
What I assume is that if I override the default, the stored procedure will process what I put in the @BeginDate and @EndDate parameters.
The where statement looks like:
and (m.BeginDate >= @BeginDate) and (m.EndDate <= @EndDate)
I'm using Reporting Services 2005 and SQL Server 2005.
Thanks, Iris
I am using SQL 2005 SSIS. I need to do a data conversion for a date field in a txt file. I used the import wizard to bring my txt file into SQL 2005 but didn't convert the date. The date is displayed in the flat file as 20070612. Can someone help me convert the date. I did add an OLE DB Source to the Data Flow screen and selected command what do I do next and what do I write?
View 9 Replies View Related
Hi,
I am trying to get all the row counts of source and target databases, then insert into a rowcounts table
and getting all the data about package name,start time etc and insert into logs table
How can I do it?
thanks,
Gokhan
In good old fashioned DTS there was the ability to perform custom transformations using activeX / vbscripty type language - does this still exist or are we stuck with the derived column editor?
View 3 Replies View RelatedDoes anyone have a way of finding all parameters with default values, for any stored procedure in a database?
aTdHvAaNnKcSe!
I have a table, tbl1:create table tbl1 ([field1] [char] (16) NULL DEFAULT (' '),[field2] [char] (6) NULL DEFAULT (' ')).When I do a select * into tbl2 from tbl1, tbl2 does not have defaults.Is there any settings I have to keep on when I do a select * into?Any help will be appreciated.
View 2 Replies View Related
Thanks in advance for fielding my question!
I have parameters (type=datetime) on a report whose values are populated from a query. This query just pulls a list of all the dates (all Sunday dates) in a table. I have the parameter set up to have the date as the value and then a string representation of the value as the label (ie 2007-11-25) to make it easier for the user.
I want to set up a default for this parameter that essentially takes today's date and calculates the previous Sunday's date so the parameter drop down defaults to last Sunday's date.
My expression works fine just to display it on the report in a text box. It calculates and displays last Sunday's date perfectly. BUT, it doesn't work when I use it in the expression for the parameter default - Im' assuming because the latter cares about data type?
here's my statement:
=Iif(Now.DayofWeek=1,dateadd("d",-1,Now),(Iif(Now.DayofWeek=2,dateadd("d",-2,Now),(Iif(Now.DayofWeek=3,dateadd("d",-3,Now),(Iif(Now.DayofWeek=4,dateadd("d",-4,Now),(Iif(Now.DayofWeek=5,dateadd("d",-5,Now),(Iif(Now.DayofWeek=6,dateadd("d",-6,Now),(Iif(Now.DayofWeek=7,Now,0)))))))))))))
In short all those if statements calculate the day of last Sunday's date based on Now()...so if it's Monday, subtract 1, if it's Tuesday, subtract 2...etc.... BUT this returns the time also - argh! Formats!
How can I format this so it will equate to my oracle date populating in my parameter list? Do I need to match the output of the above statement to the LABEL (ie, string) or the actual VALUE (ie, date). I've tried both. I've hacked at this thing for an hour and I'm sure it's so obvious!
Thanks!
J
Hi guys,
I have a problem when configuring SharePoint and RS on two boxes. (If install everything in one box is fine)
I have SQL SP2 + RS in one server and SharePoint on the other server. I've followed every step in the SQL 2005 Online Book to configure the RS and SharePoint including:
How to: Install the Windows SharePoint Services Object Model on a Report Server Computer. (Done)
How to: Configure Service Accounts (Reporting Services Configuration). (Done)
However, I got an error when click "Set server defaults" in SharePoint Central Admin:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.
In ReadMe of SQL SP2, see below: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm#_rsshare_known_problems_7
---
6.4 Service account requirements for Reporting Services
Restrictions on using built-in accounts apply to some deployment topologies of Reporting Services that include a report server running in SharePoint integrated mode. The following combination of factors will result in service account requirements:
The report server is integrated with a SharePoint farm comprising more than one computer.
The report server and SharePoint Central Administration Web site run on separate computers.
In this scenario, if either the Report Server Web service or Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration will not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site will result in the following error:
"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'."
To avoid this error, choose one of the following approaches:
On the computer that hosts the report server, continue to run the Report Server Web service as NetworkService and add the built-in account, such as NT_AUTHORITYNetworkService to the WSS_WPG Windows group.
Configure the service accounts to run under a domain user account as follows:
Start the Reporting Services Configuration tool and connect to the report server.
Click Windows Service Identity, click Windows Account, type a domain user account, and click Apply.
Click Web Service Identity, for Report Server, click New, type an application pool name, click Windows Account, type a domain user account, and click Apply.
Reset IIS.
Restart the Windows service.
---
I did try it out but still get the error when clicking "Set server defaults" link in SharePoint Central Admin.
Is there something wrong with the two boxes installation???
Hope the MS guys can help.
Paul
What is the difference between ‘Fuzzy Lookup Transformations ‘ and ‘Lookup Transformations in ssis .any real time senario for better understanding
View 1 Replies View RelatedHello all.I am trying to write a query that "just" switches some data around soit is shown in a slightly different format. I am already able to dowhat I want in Oracle 8i, but I am having trouble making it work inSQL Server 2000. I am not a database newbie, but I can't seem tofigure this one out so I am turning to the newsgroup. I am thinkingthat some of the SQL Gurus out there have done this very thing athousand times before and the answer will be obvious to them.This message is pretty long but hopefully gives you enough informationto replicate the issue.There are 3 tables involved in my scenario. Potentially a lot more inthe real application, but I'm trying to keep this example as simple aspossible.In my database I have many "things". Let's call them "User Records"(table: users) for this example. My app allows the customer to createany number of custom "Extra Fields" (XF's) for a given User Record.The Extra Field definitions are stored in a table which we can callattribs. The actual XF values for a given user record are stored in athird table, let's call it users_attribs.users_attribs will look something like this (actual DDL below.)UserID | ExtraFieldID | Value--------------------------------------User_1 | XF_1 | hamUser_1 | XF_2 | eggsUser_2 | XF_1 | baconUser_2 | XF_2 | cheeseUser_3 | XF_2 | onionsThe end result is that I want a SQL query that returns something likethis:UserID | XF_1 | XF_2-------------------------------------User_1 | ham | eggsUser_2 | bacon | cheeseUser_3 | NULL | onionsPotentially there would be one column for each extra field definition.One interesting question is how to get a dynamic number of columns toshow up in results, (so new XF's show up automatically) but I'm notworried about that for now. Assume I will hard-code a specific set ofextra fields into my query.The key here is that all users must show up in the final result EVENIF they don't have some extra field value defined. Since User_3 inthe example above doesn't have an XF_1 record, we see a NULL in thatcolumn in the final result.With Oracle I am able to accomplish this via an Outer Join, and I knowSQL Server supports Outer Joins, but I can't seem to make it work. Inever version I have tried so far, if any user is missing any extrafield value, the entire row for the user goes "missing", and that ismy problem.It seems like one possible solution would be to just go ahead andpopulate the users_attribs table with a NULL value for thatcombination of user ID and extra field ID, basically adding a new rowlike this:UserID | ExtraFieldID | Value--------------------------------------User_3 | XF_1 | NULLI would like to avoid that if possible, for a number of reasons,particularly the question of *when* that NULL would be added. I don'twant my report to touch the database and add stuff at reporting timeif at all possible. In Oracle, I seemingly don't have to, and I wantto get to that point on SQL Server.So, here is some specific DDL to recreate this scenario:CREATE TABLE users (user_id varchar(60), username varchar(60));-- Extra Field (attribs) definitionsCREATE TABLE attribs (xf_id varchar(60), xf_name varchar(60));-- Extra Field values for UsersCREATE TABLE users_attribs (user_id varchar(60), xf_id varchar(60),val varchar(60));-- populate the sample tables-- sample User recsINSERT INTO users VALUES ('U_1', 'John Smith');INSERT INTO users VALUES ('U_2', 'Mary Rogers');-- sample extra field definitionsINSERT INTO attribs VALUES ('XF_1', 'Extra Field 1');INSERT INTO attribs VALUES ('XF_2', 'Extra Field 2');INSERT INTO attribs VALUES ('XF_3', 'Extra Field 3');-- sample values for User Extra Fields (XF's)-- U_1 ("John Smith") has complete values for each XFINSERT INTO users_attribs VALUES ('U_1', 'XF_1', 'XF_1 value forU_1');INSERT INTO users_attribs VALUES ('U_1', 'XF_2', 'XF_2 value forU_1');INSERT INTO users_attribs VALUES ('U_1', 'XF_3', 'XF_3 value forU_1');-- U_2 ("Mary Rogers") only has one value, missing the other two..INSERT INTO users_attribs VALUES ('U_2', 'XF_2', 'XF_2 value forU_2');Now, I can get what I want on Oracle, provided that I define an newview that joins the three tables together, then do a separate query onthat view that does an outer join. I could dispense with the view,but I don't want to hard-code the XF ID's into the query. I am finewith hardcoding the XF names, though. (Long story.)-- Create a User Extra Field view that joins Users-- extra field definitons (attribs)-- and values (users_attribs.)CREATE VIEW u_xf_view ASSELECT u.user_id, at.xf_name, uxf.valFROMusers u,attribs at,users_attribs uxfWHEREuxf.user_id = u.user_id ANDuxf.xf_id = at.xf_id-- Oracle-only outer join syntax works if you use the view:SELECTu.username as "User Name",uxf1.val as "Extra Field 1 Value",uxf2.val as "Extra Field 2 Value",uxf3.val as "Extra Field 3 Value"FROMusers t,u_xf_view uxf1,u_xf_view uxf2,u_xf_view uxf3WHEREuxf1.user_id(+) = t.user_id ANDuxf1.xf_name(+) = 'Extra Field 1' ANDuxf2.user_id(+) = t.user_id ANDuxf2.xf_name(+) = 'Extra Field 2' ANDuxf3.user_id(+) = t.user_id ANDuxf3.xf_name(+) = 'Extra Field 3';-- RESULTS (correct):User Name Extra Field 1 Value Extra Field 2 Value ExtraField 3 Value------------- ------------------------ ------------------------------------------------John Smith XF_1 value for U_1 XF_2 value for U_1 XF_3value for U_1Mary Rogers NULL XF_2 value for U_2 NULL2 Row(s)So far I have not been able to get the equivalent result in SQLServer. Like I said, I am really hoping to avoid populating thoseNULL values. Can anything think of a way to replicate Oracle'sbehavior here? I have tried a number of variations on the ANSI joinsyntax instead of Oracle's (+) operator, but everything I tried so farhas only yielded a row when ALL extra fields are populated (or evenworse behavior.)I greatly appreciate any assitance you may be able to give. I would behappy to provide any additional information if I forgot to mentionsomething important. I apologize in advance for any broken / wrappedlines. Thank you for taking the time to read this.I'm going to be out of town for the next week or so, so I won't checkfor a response until then, but as soon as I get back home I will checkback in the newsgroup.Thank you!!Preston Landerspibble (at) yahoo (dot) com
View 2 Replies View RelatedWe've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar. Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value? It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.
View 1 Replies View RelatedHi,
I Just wanted know that where is the Default and Constraint values are stored in SQL server or DB2 sytem Tables?
I am doing a little research on Google about this topic and I ran intothis thread:http://groups.google.com/group/micr...dc13d4ee6758966I read SQL Server MVP Louis Davidson's post saying:"Actually they are more likely to drop the concept of bound defaults.Constraints are the standard way to do this, and really should be the wayyou create defaults anyhow."Even I read in the Microsoft SQL Server Introduction (SQL 7 bookpage 244, however we're using SQL Server 2000):"Constraints define rules regarding the values allowed in columns and arethe standard mechanism for enforcing integrity, preferred over triggers,rules, and defaults. They are also used by the query optimizer to improveperformance in selectivity estimation, cost calculations, and queryrewriting."Why constraint defaults are better? The second sentence about constraintshaving better optimization, I am guessing they don't mean this aboutDefault Constraints, rather the other type of constraints?Because I don't see how a Default Constraint have anything to do withperformance? Isn't default only to do with new records being created?At work we are setting all tables' columns to have constraint defaultsof 0 or ' ' (space character) in order not to have any column with theNULL value. Therefore we have dozens of files containing statements like:alter table TABLE1 add constraint TABLE1_ID_DFDEFAULT(' ') FOR IDgoalter table TABLE1 add constraint TABLE1_QUANTITY_DFDEFAULT(0) FOR QUANTITYgoFirst I was thinking to create 3 SQL Defaults called:DefaultZeroDefaultSpaceDefaultDateand then bind these defaults to all the columns of all tables excludingprimary keys. After creating the tables I would enumerate throughall the columns and bind one of these three Defaults based on theirdatatype:number = DefaultZerotext type = DefaultSpacedate type = DefaultDateAnd then unbind the ones that we specifically need to specify otherdefault values.So my question is should I do this by using sp_binddefault or stickwith using Default Constraints inside a table/columns loop code?Thank you
View 10 Replies View RelatedTo make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with CheckpointRestart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.
Is there a way to control the defaults that the SSIS IDE uses?
I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.
I created the following Default
CREATE DEFAULT HelloWorld AS 'HelloWorld'
Now, tell me how can I get the information for this default? I can find it in sys.objects and it says its a DEFAULT CONSTRAINT, but sys.default_constraints does not show anything. Anyone?
Thanks
I am at my wits end here. For Replication the Books Online clearly state:
"The option to allow transformations is set at the time you create a publication"
However, I cannot find any options that allow me to do this in the Create Publication Wizard.
Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.
Where am I going wrong?