if you extract time  ( say, start, end)  stored as float from sql server 2000  database ,

date(end)-date(start)   returns  the time difference measured by number of days in float format   (say.  0.0625  = 90m)

(date(end)-date(start)) * 24  returns the hour difference in float format

(date(end)-date(start))*24*60 returns the minute difference in float format

for example , in sql server , select CAST ( 40358 as datetime)   returns 2010-07-01 00:00:000,

but in Qlikvew,   expression timestamp( 40358)  = 2010-06-29 12:00:000 am.

Qlikview  gets he same result as what Excel gets

Problem:  if we are using 64bit Qlikview, you may not be able to  find any   64-bit compatible ODBC driver for Microsoft Access  in Qlikeview’s ‘Data Link Properties’ window. There is  a Microsoft Access Database Engine 2010 Redistributable, but it is for  reading  data from and writing  data to Office 2010 system files such as Microsoft Access 2010 (mdb and accdb) files and Microsoft Excel 2010 (xls, xlsx, and xlsb) files. Further more,.Alought QlikView works with both 32-bit and 64-bit ODBC drivers. the 32-bit version of QlikView will only work with the 32-bit ODBC

drivers and 64-bit QlikView will only work with the 64-bit ODBC drivers. Therefore we have to use the 32-bit version of QlikView now.

Solution:

 To check what 32-bit ODBC drivers you have installed, Run C:\Windows\SysWOW64\odbcad32.exe

If you don’t have Microsoft SQL ODBC Driver  and Microsoft Jet ODBC Driver,

download it from here.

 If the  .mdb file has a database level password , then you have to use Microsoft Jet ODBC Driver. Following the following steps

1. Start  32 bit Qlikview,  and go to  Edit Script, on the data tap of  Edit Script Window, make sure  OLDB in the database section is checked , then Click Connect, The ‘Data Link Properties’ window will open.
2. On the provider tap, choose Microsoft Jet ODBC Driver.
3. On the Connection tap, select or enter a database name and path
4. On the All tap,  input the database password
5. Click button to till Create Select Statement windows open.

 reference links

1. http://social.msdn.microsoft.com/forums/en-US/netfx64bit/thread/dfbd0406-bc69-436e-998b-7fc454012f91/

http://msdn.microsoft.com/en-us/library/cc280478.aspx

Debugging -> Debugging Options -> Run64BtRuntime -> change to False In addition if you use SQL Agent go to the job step properties and then check the 32 bit runtime.

I created a package that extract data from an Excel file into table in a database with BIDS 2008. When I am trying to preview the the data from Excel Source in Data Flow Task, I get the following error:

===================================

There was an error displaying the preview. (Microsoft Visual Studio)

===================================

Index and length must refer to a location within the string.
Parameter name: length (mscorlib)

Why:

Space in Excel worksheet name

Solution: 

deleting Spaces in Excel worksheet name

SSIS does not contain a built in component for generating surrogate keys but there is still a mechanism for doing it – the Script Component. The Script Component allows us to modify the data in a data flow path using managed code and we can use it to generate surrogate keys.

1. Creating the transformation

When you drag a script component from the toolbox to the design surface you will be prompted as to whether the component is to be a source adapter, a destination adapter or a transformation. Select Transformation.

2. Configuring the transformation metadata

Your script component should contain 1 input and 1 output. This is to be a synchronous transformation so the SynchronousInputID property of the output must be the same as the ID property of the input. The output should have 1 column which in this instance I have called SK.

In the case of a synchronous transformation no output buffer is required; the input buffer is used by the output. Therefore, any columns that we create on our output actually appear as if they are part of the input buffer as we will see later in the script

A good rule of thumb for synchronous script transformations is that you should only add columns to the output which do not already exist in your input because the input columns will flow through the script transformation in any event

Note in the screenshot above the SynchronousInputID property of the output is set to the ID of the Input thus indicating that this is a synchronous transformation.

3. Building the script

We are going to use managed code to populate SK with an integer that acts as a surrogate key.

The code looks like this:

mports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
<CLSCompliant(False)> _
Public Class ScriptMain
Inherits UserComponent
Dim counter As Integer

Public Sub New()
counter = 0
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
End Sub

Public Overrides Sub PostExecute()
MyBase.PostExecute()
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
‘
‘ Add your code here
‘
counter += 1
Row.SK = counter

End Sub

End Class

There are four things to note about what is going on here:

• We have declared a variable called counter that will contain a value to be outputted as the surrogate key.
• We have initialized counter within the New() method which gets called just once on each execution
• We increment counter within the Input_ProcessInputRow() method which gets called for each row in the input buffer
• We are outputting the contents of the input buffer to the component output along with our surrogate key value from counter

 ”C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe” -o export -url http://mydomain1/sites/mysite1  -filename C:\mypath\all.dat -overwrite -includeusersecurity -versions 4

 ”C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe”  -o deletesite -url http://mydomain2/sites/mysite2

“C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe” -o createsite -url http://mydomain2/sites/mysite2   -ownerlogin administrator -owneremail yongru.zhang@utoronto.ca

“C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe” -o import -url http://mydomain2/sites/mysite2  -filename C:\david\sharepoint\all.dat -includeusersecurity

Create a ETL package, which move data in one table (Service_Provider_Master) to another table (temp_Sevice_Proivider_Master) in the local database

Step.1 , On Local Server (WKYYZ23-ARMS20),  Create a new  ETL project, save the solution in C:\David\David_Deploy_Example ,  rename the default  package as table_to_table_data_move.dtsx.

Step.2 Create a new connection manager (WKYYZ23-ARMS20.airportBIDM_ODS) to connect to database airportBIDM_ODS

Step.3 Create a dataflow task which move data in one table (Service_Provider_Master) to another table (temp_Sevice_Proivider_Master) in  the  local database (airportBIDM_ODS_15jun09-Full) .

Create the configuration file for the package which you want to deploy to another server.

Step.4   In the “Control Flow” tab, right click on the design surface and select “Package Configurations…” from the pop-up menu.

Step.5  . Tick the check box “Enable package configurations”

Step 6. Click the “Add…” button, to open the “Package Configuration Wizard”

Step 7 . In the “Configuration Type” combo box select “XML Configuration File”

Step 8 . Click “Browse…” to select a location for the XML configuration file

Step 9. Specify a file name in the “Select Configuration File Location” dialog box and click Save. Here I selected C:\David\David_Deploy_Example\table_table_data_move_config.dtsConfig

Step 10 . Click “Next” in the package configuration wizard

Step 11 . In the “Objects” tree structure, expend folder “connection managers”…… “WKYYZ23-ARMS20” ……properties, tick the objects “connection string”  for which you want to generate the configuration file, and click “Next”,

Step 12 . Provide a name for this configuration,       table_to_table_data_move_config_file and click “Finish”

Step 13 In Windows explore, go to C:\David\David_Deploy_Example and open file “table_table_data_move_config.dtsConfig” with any XML editorL:

<?xml version=”1.0″?>

<DTSConfiguration>

       <DTSConfigurationHeading>

              <DTSConfigurationFileInfo GeneratedBy=”WKYYZ23-ARMS20\Arms” GeneratedFromPackageName=”table_to_table_data_move” GeneratedFromPackageID=”{1EB78EDA-E5D7-4843-90CE-4203730E9288}” GeneratedDate=”11/5/2009 1:40:22 PM”/>

       </DTSConfigurationHeading>

       <Configuration ConfiguredType=”Property” Path=”\Package.Connections[WKYYZ23-ARMS20.airportBIDM_ODS].Properties[ConnectionString]” ValueType=”String”>

              <ConfiguredValue>Data Source=WKYYZ23-ARMS20;Initial Catalog=airportBIDM_ODS_15jun09-Full;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application-table_to_table_data_move-{77EF74ED-52CF-4B5A-B785-5453E3462CE9}WKYYZ23-ARMS20.airportBIDM_ODS_15jun09-Full;Auto Translate=False;</ConfiguredValue>

       </Configuration>

</DTSConfiguration>

Step 14  Copy the parckage and configuration file to antoehr machine (AC-6F689A61BAA3) on which SQL Server 2008 is installed and the database we want to use  is airportBIDM_ODS_backup

Step 15, On the target machina   Open the configuration file and change the ConfiguredValue as following
<ConfiguredValue>Data Source= AC-6F689A61BAA3;Initial Catalog= airportBIDM_ODS_backup;Provider=SQLNCLI10.1;Integrated Security=SSPI;Application-table_to_table_data_move-{77EF74ED-52CF-4B5A-B785-5453E3462CE9}WKYYZ23-ARMS20.airportBIDM_ODS_15jun09-Full;Auto Translate=False;</ConfiguredValue

Appendix

Guidelines on using XML configuration files in SSIS:

Connection and XML configuration file naming conventions: When creating a new OLE DB database connection, use the following naming convention for your connections:

SERVERNAMEdatabasename

Note: You will have to rename your connection in the connection manager, after the connection is created.

For example, a connection to a server named LDNSrv1 and MyDB1 database should look like:

LDNSRV1mydb1

Notice that the server name is in CAPS and the database name is in lower case. However, this is just a guideline, and feel free to go with the naming convention that you are comfortable with.

Remember to follow the same casing standard everywhere, as SSIS connection names are case sensitive.

Use the same naming convention for the XML configuration files. Make sure your XML configuration files have the .dtsConfig extension. By default SSIS looks for this extension. But any other file extension will work too.

For example, the XML configuration file for Nyk1 server and Stocks database should be named:

NYK1stocks.dtsConfig

Managing XML configuration files: Store all the XML configuration files in a standard location on all servers. For example, at F:\UDL folderCreate a separate XML configuration file for each server and database combination. This approach is better than storing multiple server configurations in a single XML file. The reason being, if you need to edit connectivity information for a single server, you will directly edit the XML file for that server only. This reduces the chances of accidentally changing information for other servers.

If you are connecting to more than one server in your SSIS package, add the relevant XML configuration file for each server and database combination. You can add multiple configuration files from the “Package Configurations” screen, as shown in the below image:

For example, if your package is connecting to Srv1.db1 and Srv2.db2, then you would add the following XML configuration files to your package:

SRV1db1.dtsConfig
SRV2db2.dtsConfig

Always reuse existing XML configuration files, if one exists for your server and database combination. This is the sole reason behind configuration files, as you will have to update connectivity information in only one place. If there isn’t a configuration file already created for your server and database combination, create a new configuration file using the below templates and follow the described naming convention.

For Windows authenticated connection to SQL Server:

<DTSConfiguration>

               <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">

                              <ConfiguredValue>Data Source=[SRVNAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>

               </Configuration>

</DTSConfiguration>

For SQL Server authenticated connection to SQL Server:

<DTSConfiguration>

               <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">

                              <ConfiguredValue>

                                              Data Source=[SRVNAME];Initial Catalog=[DBNAME];User ID=[LOGIN];password=[PWD];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False

                               </ConfiguredValue>

               </Configuration>

</DTSConfiguration>

Note: In the above templates, square brackets [] are not required except around the name of the connection. Do not surround server name, database name, user name and password with square brackets

Populating global variables using XML configuration files: When dealing with Active directory shares or file paths (for connections to source and target text files or data files), do not hardcode the domain names or machine names. Instead, use variables. These variables can be populated from the XML configuration files. Once a variable is created in the SSIS package, its value can be populated from the XML configuration file. For example, here’s what you need in the configuration file, to populate the variable called Domain with a value of “NykLive”:

<DTSConfiguration>

               <Configuration ConfiguredType="Property" Path="\Package.Variables[User::Domain].Properties[Value]" ValueType="String">

                               <ConfiguredValue>NykLive</ConfiguredValue>

               </Configuration>

</DTSConfiguration>

Note: Variable names are case sensitive too. So make sure you follow the same case when referring to the variables in the configuration files, script tasks etc.