billinkc

SET NOCOUNT ON;

CREATE TABLE ##Zane
(
    db_nm sysname
,   obj_nm	nvarchar(128)
,   object_id	int
,   definition	nvarchar(max)
,   uses_ansi_nulls	bit
,   uses_quoted_identifier	bit

billinkc

Get it
http://www.rstudio.com/ide/download/desktop

http://en.wikipedia.org/wiki/R_(programming_language)
	In R, the widely preferred assignment operator is an arrow made from two characters "<-", although "=" can be used instead.
Set up
install.packages('ggplot2')
install.packages('RSocrata')

Lurning

billinkc

USE [msdb]
GO
DECLARE @jobId binary(16)
EXEC msdb.dbo.sp_add_job
    @job_name = N'MarkV'
,   @enabled = 1
,   @notify_level_eventlog = 0
,   @notify_level_email = 2
,   @notify_level_netsend = 2
,   @notify_level_page = 2

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FileConnection Name="RunLong.dtsx" FilePath="C:\sandbox\VarigenceForums\VarigenceForums\RunLong.dtsx" />
    </Connections>
    <Packages>
        <Package Name="ZanePOC" ForcedExecutionValueDataType="Empty" Language="None" ConstraintMode="Parallel" SsisPackageType="5" VersionBuild="6"  CreationDate="2014-05-06T12:20:02">
            <Variables>
                <Variable Name="FullTarget" DataType="String" IncludeInDebugDump="Exclude">C:\sandbox\VarigenceForums\VarigenceForums\RunLong.dtsx</Variable>
            </Variables>
            <Tasks>

billinkc

# http://www.r-tutor.com/r-introduction/data-frame
# http://stackoverflow.com/questions/23572621/what-is-the-fastest-way-to-write-to-a-db-table-from-r
# http://www.statmethods.net/input/dbinterface.html
library(RODBC)
myconn64 <-odbcConnect("RM_ODBC_64", "", "")

src <- sqlQuery(myconn64, "SELECT T.* FROM sys.tables AS T CROSS APPLY (SELECT 1 FROM sys.all_columns) AS AC(x);")

#Create target table
cmd <- sqlQuery(myconn64, "IF NOT EXISTS(SELECT 1 FROM sys.tables T WHERE T.name ='r_table' AND t.schema_id = SCHEMA_ID('dbo'))

billinkc

-- Find all messages associated to the last failing run
SELECT
    OM.operation_message_id
,   OM.operation_id
,   OM.message_time
,   OM.message_type
,   OM.message_source_type
,   OM.message
FROM
    SSISDB.catalog.operation_messages AS OM

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2012;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="LoopDeDatabases">
            <Variables>
                <Variable DataType="String" Name="CurrentDatabase">tempdb</Variable>
                <Variable DataType="String" Name="QuerySource">SELECT D.name FROM sys.databases AS D;</Variable>
                <Variable DataType="Object" Name="rsResults"></Variable>

billinkc

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null

#this allows the debug messages to be shown
$DebugPreference = "Continue"

# Retrieves a 2012 Integration Services CatalogFolder object
# Creates one if not found
Function Get-CatalogFolder
{
    param

billinkc

IF @@Error = 0
BEGIN
	IF @@RowCount = 0
	BEGIN
		RETURN 1
	END
	ELSE
	BEGIN
	    RETURN @@RowCount
	END

billinkc

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection ConnectionString="Provider=SQLNCLI10.1;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=msdb" Name="CM_OLE_DB" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="swasheck">
            <Variables>
                <Variable Name="instanceID" DataType="Int32">0</Variable>
            </Variables>
            <Tasks>