SSIS Boolean Questions
Oct 24, 2006
Please write YES/NO against point number in your answer i.e. for example:
1) YES, 2) YES, 3) NO, 4) YES etc.
We have source SQL Server 200 databases in one instance and target SQL Server 2005 in another instance both being on different windows 2000 server systems.
Target OLAP DB design is derived from source OLTP.
Most master & transaction tables are as is but some target tables are a result of merging source tables.
We need to do SSIS packages based migration using data flow task based on column mapping & queries using Business Intelligence Development Studio (BIDS). There are no transformations required.
1) Can I group packages under project into sub-groups i.e. super packages?
2) Can I execute a set of packages together which are logically/physically grouped?
3) Can we execute set of ordered packages/individual packages from command line and even
stored procedure?
4) Can we maintain transaction integrity across the entire set of ordered packages we execute?
5) Can we send a mail to mail id(s) in case of events like OnError etc.
6) Can we schedule the packages?
7) Does SSIS provide via BIDS a way to compare data in source with target to decide what got
inserted/deleted/updated and based on that do the necessary?
8) Can SSIS packages be developed including code for data encryption?
9) Can we pass parameters to packages that get called from stored procedure?
10) Can we call stored procedure/function in SSIS package?
View 5 Replies
ADVERTISEMENT
Mar 4, 2008
Hi all,Can you please share some interview questions that can be expected on SSIS 2005?
View 8 Replies
View Related
Mar 23, 2007
I am using SAS Dataset and loading in to SQL server 2005.Now i want to delete columns which have 0 values and null values.Can anyone guide me how to do this?thank you.
View 3 Replies
View Related
Apr 11, 2008
Hi,
I was wondering if anyone know how to do Dynamic Properties Tasks in SSIS? And, does anyone know how to create a global database in SSIS so I won't have to change all of the database names in my package? I'm sorry, I'm new to SSIS and I'm just really frustrated. Thank you.
View 2 Replies
View Related
Mar 15, 2007
Hello,
I realize that I am confused about SSIS and security.
In BIDS, I work on and modify my packages. That part I understand. Then, I want to build my project, then deploy to SQL Server 2005. I know how to do that too (for the most part, please see below):
My confusion arises around the "Protection Level" options in the package properties. Right now I have everything set to "EncryptSensitiveWithUserKey". My understanding is that I need to change this in order to run my packages from SQL Server jobs, because only the creator of the package can currently run the job.
So my question is, since I want to deploy to SQL Server, don't I want to change the "Protection Level" to "ServerStorage"?
However, it will not let me change the protection level to ServerStorage. It says "The protection level, ServerStorage, cannot be used when saving to this destination"... presumably because it is attempting to save to the file system.
However, even when I built out my packages and saved to SQL Server, I could not change the protection level either.
Which is why I am really confused... Once you save your packages to SQL Server, how do you make changes to the packages, so that the changes are reflected in the packages stored on SQL Server?
There is some concept I am not understanding here.
Thanks for any help
View 8 Replies
View Related
Sep 28, 2007
I have some questions on SSIS tool I tried to read many docs to understand SSIS.
Now I am looking forward for below questions.
How to use SSIS for :
1. How to Export data from SQL Server 2005 in to Access database using SSIS?
2. How to Import data from Access .mdb file to SQL Server 2005 database using SSIS?
3. How to Import Oracle10g data in to SQL Server 2005 databvase using SSIS?
4. How to Import Oracle10g Data to Access file using SSIS?
5. How to Import SQL Server 2000 data in to SQL Server 2005?
It will be great to have step by step explanation.
Thanks in advance.
View 1 Replies
View Related
Nov 7, 2006
0) We are migrating data from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
1) We have an data flow task SSIS package.
2) We managed to send a mail with various variables & their values in subject or body of mail.
3) How do we send mail with details of Error/Warning using OnError/OnWarning events?
4) Can we have bad tables i.e where records which failed to migrate get inserted into. If yes, what are the steps to implement this.
5) Can we have a mix of in-built & custom logging. Is logging provider necessary to do logging. What logging provider to use?
6) Where are the errors that come during build, deploy, install and execute stored? Is there a log file(s) & what it their location?
7) Please post links if that is better.
View 3 Replies
View Related
Jan 18, 2008
I have been working with SSIS and find myself stopped on what seem like very basic tasks.
Am I right in thinking that, if you want to import a flat file into a SQL datatable, in most PC based systems, that you have to manually cast EVERY SINGLE COLUMN to unicode string, if you don't want to get the 'cannot convert between unicode and non-unicode string data types'? I mean, this is surely a VERY COMMON task, taking a CR/LF delimited ASCII file and dumping it into a database tables? What if you have 50 fields to import?
Am I the only one who finds it very difficult to cast a string from a textfile to a date format? At this point, I always just dump the date to a 8 character string and save it that way, and use a SQL view to cast it to a date. At least SQL Server can do 'basic date conversions'. Is there any way to freeze execution and find out what data was provided to a derived column, if you're trying to do some substring operations and hodge together a date? I couldn't find any. I usually put a extra column in a datatable and plunk some data there to find out 'what it looked like', how else can you debug an SSIS program?
Is there a planned service pack or update to SSIS that will make this more usable? Does Microsoft ever intend to implement an UPSERT type module? Or a File Move operation that handles directories that fill up with log files, each of which have to be added to a datatable and then moved to an archive directory (surely, another very common task - I figured out how to accomplish this but it is far from intuitive, the way it currently works). Sorry for grumbling, but honestly, I find turnaround time to develop a very simple import program is RIDICULOUS. It's quicker to write a COBOL program to issue a bunch of formatted SQL UPDATE statements than using this application.
View 19 Replies
View Related
Nov 27, 2006
Could some of the expert(s) list interview questions that might come up when applying for ETL/SSIS developer I or II positions.
Thanks
View 39 Replies
View Related
Dec 8, 2006
I am using SSIS with Transaction, and I met a lot of questions as below:
1.IF DTC doesn't support ADO.NET Connection?
Please check the ADO.net Connection's property, it has a property named like: DTCTrancactionSupport. But it's always set to False and unable to edit.
IF the answer is NO, HOW could I bound all my Tasks into one Transaction? You know, I have some Tasks with SQL connection, and I have also some Script Tasks, witch need the ADO.net connection.
2.IF DTC required to run in both the Destination Server and the Source Server?
I have a Dataflow Task, and take some data from a table in a Source Server to a Destination Server. The DTC doesn't run on the Source Server. I found the Dataflow Task hangs when I use the SSIS Transaction(just set the Task's transactionOpion to Required, Only one Task in the Package).
3.IF DTC doesn't support RetainSameConnection?
Many people say that. Really?
4.When a DTC transaction is running, if the table in it could not be modified by process outside of the transaction? For example, a package with a DTC transaction, and there is a SQL Task inside, when it is running, could I modified the same table's data manually(in the Sql Sever Management Studio)?
View 10 Replies
View Related
Dec 4, 2007
Hello all, this is my first post, I have a couple of basic questions regarding the use of SSIS. I have used SSIS for a couple of years for on off table loads but never implemented production jobs with it so please bear with these questions.
1. When making scripts that connect to one or more databases, is there a way to store the userid and password somewhere besides embedded within the package, such as a web.config file, this would make routine password changes much more manageble.
I guess it was only one question but any help would be greatly appreciated.
Thanks.
View 1 Replies
View Related
Jan 29, 2008
I have a question regarding a DTS (SQL server 2000 package) to SSIS (SQL SERVER 2005 package) migration.
which is the best forum to post my question ?
View 1 Replies
View Related
Mar 27, 2007
I have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
View 3 Replies
View Related
May 10, 2007
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
View 12 Replies
View Related
Jun 12, 2008
I have a stored procedure that returns 1 or 0 as one of the column value. When i execute this stored proc and fill the Data Table in .Net, the data type of this column in Data Table is becomes integer not boolean. What should i return from stored procedure so that when i fill data table the column's datatype will be boolean NOT integer? or is there any way to change the DataType of the column in Data Table?
View 5 Replies
View Related
Mar 2, 2005
I have a control with checkboxes. The checkbox.checked property returns a boolean value. If I want to insert a record into a table that has a corresponding column of type bit, how do I do it?
I triedCType(myChkBox.checked,String), which returns the strings "True" or "False". But, I get the errorThe name 'True' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.I then tried "CAST(" & CType(myChkBox.checked,String) & ",bit)", but got the same error.
I can write a function that will return a 1 or a 0 but that seems ludicrous. Surely there must be a more elegant way to use the result of a checkbox in an SQL statement.
Thanks
Martin
View 4 Replies
View Related
Jun 30, 2005
Hello,In VB/VB.NET, I can use an expression as such to evaluate true/false:Dim blnValue As Boolean = (SomeObject.Property = "Some value")Can I do this in T-SQL?declare @var bitset @var = ?Thanks.
View 2 Replies
View Related
Apr 24, 2006
Hi,
I have a FormView with a couple of TextBoxes, a DropDownList and two CheckBoxes. I have connected a SQLDataSource to the FormView with an InsertCommand to store the values entered into the different conrtrols. My problem is that the boolean values from the two CheckBoxes doesn't get stored in the Database (SQL Server Express). The columns in the DB are of type "bit", should I choose a different type? I don't see any errors just a NULL value in the DB.
I'm probably missing the obvious taken that I'm really fresh to this, so any help would be appreciated...
Code below:
<asp:SqlDataSource
id="SqlDataSource1" Runat="Server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT TOP 1 * FROM Games"
InsertCommand="INSERT INTO Games (GameID, GameDate, Opposition, Result, FirstTeamGame, HomeGame) VALUES (@GameID, @GameDate, @Opposition, @Result, @FirstTeamGame, @HomeGame)" >
<InsertParameters>
<asp:Parameter Name="GameID" />
<asp:Parameter Name="GameDate" />
<asp:Parameter Name="Opposition" />
<asp:Parameter Name="Result" />
<asp:Parameter Name="FirstTeamGame" Type=Boolean />
<asp:Parameter Name="HomeGame" Type=Boolean />
</InsertParameters>
</asp:SqlDataSource>
<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1" OnItemInserted="FormView1_ItemInserted">
<HeaderTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Manage" Width="90px" Runat="Server"/></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDate" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Opponent" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Result" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="First Team Game" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="Home Game" Width="80px" Runat="Server"/></th>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td><asp:Button ID="Button1" Text="New" CommandName="New" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" /></td>
</tr>
</table>
</ItemTemplate>
<InsertItemTemplate>
<table border="1" style="border-collapse:collapse">
<tr>
<td>
<asp:Button ID="Button2" Text="Insert" CommandName="Insert" Runat="Server" Font-Size="8pt" Width="45px" Height="22px" OnClick="Button2_Click"/>
<asp:Button ID="Button3" Text="Cancel" CommandName="Cancel" Runat="Server" Font-Size="8pt" Width="45px" Height="22px"/>
</td>
<td>
<asp:TextBox id="AddGameID" Text='<%# Bind("GameID")%>' Runat="Server" Font-Size="8pt" Width="60" OnDataBinding="CreateNewGame" />
</td>
<td><asp:TextBox id="AddGameDate" Text='<%# Bind("GameDate")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:DropDownList id="AddOpposition" Runat="Server" Font-Size="8pt" Width="118px" OnLoad="fillMe"/></td>
<td><asp:TextBox id="AddResult" Text='<%# Bind("Result")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:CheckBox id="AddIsFirstTeamGame" Text='<%# Bind("FirstTeamGame")%>' Runat="Server" Font-Size="8pt" Width="80" /></td>
<td><asp:CheckBox id="AddIsHomeGame" Text='<%# Bind("HomeGame")%>' Runat="Server" Font-Size="8pt" Width="80"/></td>
<td><asp:HiddenField id="Opposition" Value='<%# Bind("Opposition")%>' Runat="Server" /></td>
</tr>
</table>
</InsertItemTemplate>
<HeaderStyle Wrap="False" Font-Bold="True" />
<EmptyDataTemplate>
<table border="1" style="border-collapse:collapse">
<tr style="background-color:#E0E0E0">
<th><asp:Label ID="Label1" Text="Hantera" Width="90px" Runat="Server" Font-Size="10px" /></th>
<th><asp:Label ID="Label4" Text="ID" Width="60px" Runat="Server"/></th>
<th><asp:Label ID="Label2" Text="MatchDatum" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label3" Text="Motståndare" Width="118px" Runat="Server"/></th>
<th><asp:Label ID="Label5" Text="Resultat" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label6" Text="A-lags Match" Width="80px" Runat="Server"/></th>
<th><asp:Label ID="Label7" Text="HemmaMatch" Width="80px" Runat="Server"/></th>
</tr>
</table>
</EmptyDataTemplate>
</asp:FormView>
View 1 Replies
View Related
Feb 9, 1999
Can someone tell me if there is a boolean datatype in the SQL Server???
like yes/no not 1 or 0...
THANK YOU
View 1 Replies
View Related
Mar 31, 2006
Im wondering how to do a boolean equivilent while creating tables. here is an example:
ProgramNoNUMBER(10)NOT NULL,
Instead of Number(10) how would I make that a boolean in sql? Im sorry if this is a really easy question, but I looked around online and either was looking for the wrong thing or couldnt find it.
Well i guess thats not entirely true, I found it on wikipedia, but just swapping in BOOLEAN doesnt work. Can anyone tell me the syntax? Thanks
View 3 Replies
View Related
Feb 22, 2006
Hi,I am trying to get a 0 or 1 depending on the sucess or failure of at-sql statement, and here is what I have:Declare @boolDatabaseExists bitDeclare @chvnNewDatabaseName nvarchar(260)SET @chvnNewDatabaseName = 'NewDatabase'SET@boolDatabaseExists =(SELECT(1)FROMmaster.dbo.sysdatabasesWHEREname = @chvnNewDatabaseName)Print @boolDatabaseExistsWhat it is doing right now is that it prints a 1 for when there is amatch for the @NewDatabaseName in the sysdatabases table. If there isnot a match, then there is no value set for @ boolDatabaseExistsWhat I would like it to do is if there is no match for the@NewDatabaseName in the sysdatabases, I would like to set theboolDatabaseExists to 0I hope I am clear in my explanation.Thank youKR
View 3 Replies
View Related
Aug 29, 2007
Hi,
I have a Summary report and a Detail (drillthrough) report.
the summary report displays.
Summary report
Code Snippet
Adult | Male | Count
-----------------------------
Yes | Yes | 50
Yes | No | 9
No | Yes | 20
No | No | 50
------------------------------
| 129
When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.
The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.
Any ideas?
View 5 Replies
View Related
Nov 12, 2007
I have a unusual problem, well it is unusual becaue i cannot understand it.
I am retrieving the following data, Revenue, Sales, Commission, and calculating Commission Rate in the report itself.
I got warnings that a textbox that was attempting to calculate the Commission Rate was trying to divide by zero.
When I analysed the data, I realised that indeed there were instances when Revenue was 0.00 and therefore when calculating the commission rate, commission/revenue it returned an error.
I then thought I'd do the following in the expression field
=iif((Fields!RevenueGbp.Value = 0.00), "Zero", (Fields!Commission.ValueGbp/Fields!RevenueGbp.Value))
However this does not work. However if i tried the following Boolean query
=iif((Fields!RevenueGbp.Value = 0.00), "Zero", Fields!CommissionGbp.Value)
it does work. In fact if I use any other identifier or use two field thats add, subtract or multiplied with each other it will work. The problem only arises is when I decide to divide two fields together.
Strangely if the condition is satisfied - [Fields!RevenueGbp.Value = 0.00] - it will still try to work out the division even though it should just return Zero!
Anyone help?
View 4 Replies
View Related
Jan 3, 2008
I want to have an update statement like:update tblMydate set fieldx=fieldx+1 where code=12But then for a boolean value:So, if the current boolean value is true, I want to set it to false and vice versa, something likeupdate tblMydate set booleanx=not booleanx where code=12How can I achieve this?
View 3 Replies
View Related
Apr 28, 2008
Hi... I have a problem getting a select statement to return a boolean value from sql server... the aim is to populate a checkbox list with permissions a group of users has. The statement is an outer join as follows: select SS.PermissionName, BB.GroupId,case when BB.GroupId Is Null then cast(0 as bit) else cast(1 as bit)end IsAddedfrom clPermissions SS left join ( select PermissionName, GroupId from clvGroupPermissions where GroupId = (Select groupId from clGroups where GroupName = 'Admin')) BB on SS.PermissionName = BB.PermissionName The returned values are 0 and 1... but .NET does not seem to recognize these values as true and false...Any one had an issue like this before?
View 3 Replies
View Related
Dec 2, 2004
Anyone know how to do a bollean search on a database?
It would need to accept 'OR', 'AND' conditions.
Would it be a good idea to maybe create stored procedures and call these somehow?
Thank you in advance.
Michael O'Connor
View 1 Replies
View Related
Jun 19, 2005
Obviously, I'm new to SQL Server, because I'm still scratching my head wondering where in the world the boolean data type is. Can anyone please explain to me how boolean data types REALLY work in SQL Server? I've created a few Bit type fields, but when using a datagrid, they sometimes show up as 0 and 1 and sometimes as True/False, and I can't see a difference in how the grids (or fields(!)) are set up that would explain this. Any insight would be greatly appreciated!
View 1 Replies
View Related
Mar 3, 2006
Hello,
In an update statement, I want to take a boolean and reverse it. Is there an easy way to do this? SOmething like:
update table set boolfield = not boolfield where ID = @ID
but that doesn't work; is there anything like this where it will reverse it easily?
Thanks.
View 4 Replies
View Related
Jul 5, 2002
Hi, I am looking for an easy and efficiant way to add a boolean search function to an already existing search i have using ASP pages and MSSQL 2000 SPROCS.
Currently I have some code such as:
CREATE PROCEDURE sp_name
@var1 int = NULL,
@var2 varchar(50) = NULL
AS
SELECT blah, blah, blah
FROM sometable INNER JOIN anothertable ON anID = anID
WHERE (table1.column2 = COALESCE(@var1,table1.column2) AND table2.column4 = COALESCE(@var2,table2.column4))
and this works like a charm, if i pass a value into @var1 or @var 2 it gives back results where those crietera match, or if I pass no values in it returns all records.... anyways, you get the idea...
Now I would like to add the ability to introduce a 3rd variable, and have it run a boolean search on a varchar(2500) column. This column is a description field for an image so I would like to be able to search the descriptions by passing in something like 'car OR bike AND travel' and return all the records that have the words 'car' or 'bike' and 'travel' somewhere in the description field.
How can i do this? I am using SQL Server Standard Edition so I don't believe I have full text search functions available, (not sure if that would even help).
Thanks all for your support and guidance!
P.S. i would also like to maintain the functinality so that if all the @var# are blank it still returns all records.
View 1 Replies
View Related
Jun 24, 2004
What data type should I use as a replacement of Boolean in SQL server? I need to create a table column with a "two-state" data type and must ensure, that only one record can hold value "True" in this column... Any ideas? Thanks
View 1 Replies
View Related
Apr 18, 2008
Greetings,
Being fairly new to SQL Server, I created a new field with the data type 'bit' so I can store the equivalent of a yes/no response (0 or 1).
When I try to enter either a 0 or 1 into the field via MS SQL Server Management Studio Express table view, I get
"String not recognized as valid Boolean"
I'd appreciate if someone would let me know how I 'noobed' this up.
Thanks very much.
View 2 Replies
View Related
Apr 22, 2008
I know this is a stupid question, but I've searched and can't seem to find it.
What do you use in SQL when creating a table and the field is a "yes/no" type field? In VB isn't that a boolean data type? What is the corresponding SQL data type?
View 3 Replies
View Related
Apr 30, 2008
Hello,
Brand new to the forum and I'm hoping I can get some help from you guys here.
I'm not sure how I would go abouts doing this but I have a table that has a list of my Inventory and it looks like this.
Product Inventory
Modems 10
I want a way (whether it be a stored procedure or view) to display instead of the value of 10, that if there Inventory has a value greater than 0 then to return a boolean of YES, TRUE, or 1. How would I go abouts doing that?
Any help would be greatly appreciated.
WNDRBOY.
View 5 Replies
View Related
