SSIS - get packages versions and data

SELECT prog.project_id, f.[name], *
FROM SSISDB.catalog.packages pkg
JOIN SSISDB.catalog.Projects prog ON pkg.project_id = prog.project_id
JOIN SSISDB.catalog.Folders f ON proj.folder_id = f.folder_id
WHERE prog.[name] = N'PROJECTNAME'

SSIS, TVP: An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification"

Error:

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".

Cause:

Not fit parameter declaration.

But:

If your all parameters were declared well, but you still have this error, and one of the parameter is a TVP (Table Variable Parameter):

The TVP is the cause...

I tried to pass a SSIS Object to TVP in the stored procedure, and I've got this error.

Solution:

I pass the data to a temp table in the destination DB, and took it from there to the SP.

SSIS: resize container to proportional size

Problem:

Anyone who worked with SSIS gets crazy from the tries to resize the containers to proportional size…

Solution:

SSIS: copy package between projects

In order to quick copy a package from SSIS project to another:

1. Copy the package file from Windows Explorer.
2. Paste it on the project node in the Visual Studio.

SSIS Excel Connection Manager 64-bit error

Error message:

[Connection manager "XXX Excel Connection Manager"] Error: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

Solution:

Right click on the project name à properties

Configuration Properties à Debugging: Set Run64BitRuntime to False.

SSIS issue: Decimal fields are imported from csv files without the decimal part

Problem:

Decimal fields are imported from csv files to the database tables without the decimal part.
(12.547 --> 12.000).

The data imported via SSIS.
This is the definition ion the Flat File Connection Manager:

Solution:
Declare the fields in the Flat File Connection Manager as Numeric, with the correct precision and scale:

Why decimal declaration is not fit to decimal fields? Ask Microsoft :)

[SSISDB].[catalog].[set_object_parameter_value] Cannot find the parameter because it does not exist

What I tried to do:

Sets the value of a parameter in the Integration Services catalog.

EXEC [SSISDB].[catalog].[set_object_parameter_value]

       @object_type=20,

       @parameter_name=N'MyServerName',

       @object_name=N'MyProcess',

       @folder_name= N'MyProcess',

       @project_name= N'MyProcess',

       @value_type=R,

       @parameter_value= N'MyServerName';

GO

Error message:

[SSISDB].[catalog].[set_object_parameter_value] Cannot find the parameter because it does not exist.

Cause and Fix:

Lowercase / Uppercase of the names – environment VS SSIS parameters.

When I set the letters to be exactly the same – it worked.

EXEC [SSISDB].[catalog].[set_object_parameter_value]

       @object_type=20,

       @parameter_name= N'MYSERVERNAME',

       @object_name=N'MyProcess',

       @folder_name= N'MyProcess',

       @project_name= N'MyProcess',

       @value_type=R,

       @parameter_value= N'MYSERVERNAME';

GO

The XML source does not validate the data in the XML file against the XSD

Important to know:

The SSIS "XML source" element check validation of the input XML file,

but – check only the structure, not the data!

That mean not valid data will pass this validation check!

Is it good or not?

Depend on the case.

But don't forget it…

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/xml-source

SSIS error: The SQL command requires a parameter named, which is not found in the parameter mapping

SSIS error message:

The SQL command requires a parameter named "@aaaa", which is not found in the parameter mapping.

Cause and Solutions:

1. The parameter is really not exists. Solution: declare it.

2. Space - " " - in the mapping pf the parameter - see the picture below. Solution: delete the space.

Can't connect to Integration Services: "Access is denied." Only administrators have access to the Integration Services service

Try to connect to Integration Services:

Error message:

TITLE: Connect to Server

------------------------------

Cannot connect to ServerName.

------------------------------

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

Connecting to the Integration Services service on the computer " ServerName" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

Solution:

First of all check that SQL Server Integration Services is running on the server.

Second, ensure that the user is an administrator on the server.

After those 2 checks, the user should connect to Integration Services locally on the server.

In order to let the user connect from other computers, connect to the server (remote control) and:

Go to  Component services (under Administrative tools) --> Computers --> My Computer --> Dcom config

(you should be a local administrator in order to be able to open it).

Find "Microsoft SQL Server Integration Services (version #) --> right click, properties --> Security tab:

Press each one of the "Edit" buttons, add the user-group and let it local and remote permissions.

Then restart SSIS service.

https://msdn.microsoft.com/en-us/library/hh213130(SQL.110).aspx

If still not, check that the user/user group is assigned to the "Distributed Com Users" group:

Of course – restart SSIS services.

Now it really should work!

Export and Import / move a maintenance plan from one server to other

The maintenance plan stored in the sql server database engine menu under Management --> maintenance plan:

In order to move it to other server, we should do few steps:

1. Connect to the Integration Services of the server where the maintenance plan:

2. Right click on the maintenance plan --> Export Package:

3. Fill the package path (where the dtsx file will be saved) and press OK:

4. Connect to the Integration Services of the server where the maintenance plan will be imported to,

As before – with the same ordering – Right click on the maintenance plan --> Import Package

5. Add the package (press on the "…" button near the pacage path:

That it!

Connect to the database engine and make sure that you see the maintenance plan also there.

Note: if the maintanance plan run from a job – the job should be imported also (of course...).

Failed to execute IS server package because of error. Description: The operation failed because the execution timed out

What happened?

3 jobs that execute the same SSIS package on 3 different databases on the same server on the same time.

Most of the time it run OK, from time to time one of the job was failed.

What I checked?

In the jobs log history I saw the failed run.

In the failed step, there was no useful message, only:

Executed as user: XXX. The step failed.

To my surprise, it was in line with the sign of success was the remark about the error:

Failed to execute IS server package because of error 0x80131904. Server: MVS-SQL8, Package path: \SSISDB\...\...\XXX.dtsx, Environment reference Id: 6.

Description: The operation failed because the execution timed out.

Source: .Net SqlClient Data Provider

Started:  04:00:00

Finished: 04:00:13

Elapsed:  12.933 seconds

I wondered: Timeout after 12.933 seconds????

In the SSIS "All Executions" report this executions was not displayed.

Conclusion: the package didn’t run at all.

So, what we have here?

A job that has to execute a SSIS package.

The job was failed. The SSIS package didn't run.

Error message:

Failed to execute IS server package because of error. Description: The operation failed because the execution timed out.

What it means?

There is something in the SSISDB database that causing packages to time out before they start running.

So, what solved the problem?

I changed the scheduled time of the job to run 10 minutes apart.

CDC - Change Data Capture

http://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/

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

http://www.codeproject.com/KB/database/MSSQLServer_CDC.aspx

http://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-%28cdc%29-in-sql-server-2008/

http://www.sqlserver.co.il/?p=480

Business Keys

Business Key columns identify one or more columns that the SCD component can use to determine how Existing Dimension rows get matched up to Source Systemrows, in order to determine if any changes have occurred in the source system.

http://support.sas.com/documentation/cdl/en/etlug/62233/HTML/default/n03673ddo0y2twn114ephiuc8s5o.htm

Read about Slowly Changing Dimensions: 

http://copypastenet.blogspot.com/2011/03/slowly-changing-dimensions.html

SCD - Slowly Changing Dimensions

Slowly Changing Dimensions 

http://www.1keydata.com/datawarehousing/scd.html

http://gerireshef.wordpress.com/2010/01/29/%D7%90%D7%99%D7%AA%D7%95%D7%A8-%D7%A9%D7%92%D7%99%D7%90%D7%95%D7%AA-%D7%91%D7%98%D7%91%D7%9C%D7%90%D7%95%D7%AA-slowly-changing-dimension/

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

http://www.geekinterview.com/question_details/229

Slowly Changing Dimension in SSIS

http://netindonesia.net/blogs/kasim.wirama/archive/2007/10/25/slowly-changing-dimension-in-ssis.aspx

Kimball Method SSIS Slowly Changing Dimension Component

http://kimballscd.codeplex.com/


Business Keys
http://copypastenet.blogspot.com/2011/03/business-keys.html

Data Warehouse Process

Source DB --> SSIS --> SSAS --> SSRS

• Application Database (Source DB).

• Definition of needs and requirements
• Design the Data Warehouse
• Construction of dimensions
• Construction of fact tables
• ETL
• Extract relevant data.
• Transform data to DWH format.
• Load data into DWH.
• Relational Data Warehouse Management.

• DATA Analysis
• Dimensions and cubes in an Analysis Services solution.

• Data Presentation – Reports.

SQL Server 2008 Data Warehousing
http://www.microsoft.com/sqlserver/2008/en/us/data-warehousing.aspx
Best Practices for Data Warehousing with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719165(v=sql.100).aspx
An introduction to SQL Server data warehousing concepts (presentation)
http://searchsqlserver.techtarget.com/video/An-introduction-to-SQL-Server-data-warehousing-concepts
SQL Server Data Warehouse Cribsheet
http://www.simple-talk.com/sql/learn-sql-server/sql-server-data-warehouse-cribsheet/

Problems in process of running SSIS package from a job

Problem:

Argument for option "connection" is not valid. The command line parameters are invalid. The step failed.

Possible solutions:

• Remove "-" from the data source name
• Change /CONNECTION "DEVWIN2008R2.ItaiDW.sa";"\"  to  /CONNECTION "\"DEVWIN2008R2.ItaiDW.sa\"";"\"

Problem:

Failed to open package file due to error 0x80070003 "The system cannot find the path specified.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.

Solution:

The path will be relative to the server i.e. c: will be the disk on the server not the disk on your workstation.

Copy the package to the server, or share the folder of the package and call the shared folder.

Problem:

DTExec: Signature verification failed.  The signature status is not present.

Solution:

Check the SSIS package, it's probably failed. can be occurred because problems in the connection strings.

Problem:

Failed to open package file "D:\itai test\TestPackage.dtsx" due to error 0x80070015 "The device is not ready.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.

Solution:

Check the path of the package file.

Problem:

Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

Possible solutions:

• Protection level should be DontSaveSensetive.
• Check if you have full installation of SSIS in the computer.

If the package execution (double click on the file) was executed Successfully, and the job still failed:

In the job, in the step of the SSIS, do only:

• Choose step name
• Select type - "SQL Server Integration Services Package"
• Select the package file

Final command line will be like this:

/FILE "C:\packageTest\AssignmentsHistoryPackage.dtsx"  /CHECKPOINTING OFF /REPORTING E

How to run SSIS package from a job:

http://copypastenet.blogspot.com/2011/02/run-ssis-package-from-job.html

Run SSIS package from a job

Source DB: VM4ItaiTest, Exported Table/Process: TestTable

Data Warehouse (destination) DB: ItaiDW

1. Add a column IsExportToDW to table TestTable. (DEFAULT 0):

• 0 = new row, was not Exported yet to DWH.
• 2 = in export process.
• 1 = Exported to DWH.
• Create index for this column.

2. In the Stored-procedure that select the data for this process (that called, for this example dbo.ItaiFillTestTable), Select only records where TestTable.IsExportToDW = 2

3. Create new SSIS package TestTablePackage.dtsx:

• Source: EXEC [dbo].[spr_Itai_PowerData_FillTable_AssignmentsHistory] (in DB VM4ItaiTest).
• Destination: PowerData_AssignmentsHistory (in DB ItaiDW).

4. Create configuration file for the package.

• Edit Connection string manually.
• The package and the configuration file should be in the same folder.
• SSIS Package Configuration - http://copypastenet.blogspot.com/2011/02/ssis-package-configuration.html

5. Create a Job to export data to DWH ItaiTestDW_TestTable:

• Step 1: UPDATE TestTable SET IsExportToDW = 2 WHERE IsExportToDW = 0.
• Step 2: execute TestTablePackage.
• Step 3: UPDATE TestTable SET IsExportToDW = 1 WHERE IsExportToDW = 2.
• Schedule: Can be each X minutes.

Notes:

1. The package can be executed also from trigger on TestTable:

• Execute the package directly from the trigger (instead of Job and column IsExportToDW). Disadvantage: when the package will be failed – the trigger and also all the insertion will be failed too.
• Start the job from the trigger (don't wait to the job execution completion).

2. The package can be executed also from stored procedures.

3. In any process of the above – it's recommended to keep column IsExportToDW – in order to know if the process is failed and make it re-execute failed records.

4. To complete the solution, it's required to:

• Update column IsExportToDW (=0) when a new row is inserted (will be done automatically by default value 0 to the column).
• Update column IsExportToDW (=0) when a new row is updated.  Implement cases of row update in SSIS.
• Implement cases of row deletion in SSIS.

Problems (and solutions... (-: ) in process of running SSIS package from a job:
http://copypastenet.blogspot.com/2011/02/problems-in-process-of-running-ssis.html

SSIS Package Configuration

http://www.techbubbles.com/sql-server/ssis-package-configuration-in-sql-server-2008/