Pass SSIS Package Parameter Values From Agent Job to Run a bat File with These Variables.

1. bat file
Design a bat file to use robocopy with three parameters for MINAGE and two file names with wildcards.
The parametera are used as percentage sign and a number starts at 1, like %1 %2 and %3.

2.SSIS package
Design an SSIS package to pass variables to the bat file

Create three SSIS variables: SSIS tab >> create one int32 (varNum) and two String variables (varDBName1 and varDBName2) and assign some default values if you choose to.
Next step to connect these variables to your bat file.

Use Execute Process Task:
From Execute Process Task Editor Process tab:
Execute—-E:\X\temp\mybatfilewiththreevariables.bat
Argument leave it blank and use the Expression tab to add argument for the string of these variables in a concatenation syntax.
WorkingDirectory—E:\temp\
StandardInputVariable — pick any variable as a first one

From Execute Process Task Editor Expression tab:
Expand Expression tab >>Property Expressions Editor from Property column, pick Arguments, then expand Expression tab (click on … tab>> Expression Builder window pop up.
link all variables as a string in this manner:
(DT_WSTR, 10) @[User::varNum]+” “+(DT_WSTR, 10) @[User::varDBName2]+” “+(DT_WSTR, 10) @[User::varDBName2]

Cast variable to datatype DT_WSTR and concatenate them with +” “+ to become a string with space separate them.
You can click on Evaluate Expression at the bottom and will see the Evaluated value from your default values.
Close all open windows and save your package.

3. Agent job

Create and agent job to run the SSIS package with three parameters to pass to a bat file to use robocopy inside the bat file.
@command=N’/FILE “\”E:\packages\passmultipleSSISParameterValuesInAgentJob.dtsx\”” /CHECKPOINTING OFF /SET “\”\Package.Variables[User::varNum]\””;6 /SET “\”\Package.Variables[User::vardbname1]\””;”\”\”\”myDb1\”\”\”” /SET “\”\Package.Variables[User::vardbname2]\””;”\”\”\”mydb2\”\”\”” /REPORTING E’,

Double click on AgentJob name >> from Job Properties window>>Click on Steps from left >> Click on Edit >> Job Step Properties >> click Set values tab >>
Add Property Path and value as below for each variable:
For integer parameter
Property Path: \Package.Variables[User::varNum]
Value: 1
For string type parameter, the Value needs double quotation around the string.
Property Path: \Package.Variables[User::varNum]
Value: “varDBName”

4.Check the bat file and an example

Retrieve these parameters from your bat file in the syntax %1 %2 %3 and use them in the place to replace static value.
An example to use these three parameters to use robocopy to copy and clean files:

IF NOT EXIST \\desfolder\empty mkdir \\desfolder\empty
Robocopy \\src \\desfolder\empty /s /MOVE /MINAGE:%1 /LOG+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt
rmdir \\desfolder\empty /s /q
Robocopy \\src \\desfolder *%2*.* *%3*.* /s /z /np /MT:32 /W:5 /R:5 /log+:E:\log\%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%_%TIME:~0,2%-%TIME:~3,2%_mylog.txt

Advertisements

Dynamic Unpivot and Pivot with Case

  

 
  
create table test (emp_id int 
,[17-Jul] decimal(6,2) 
,[17-Aug] decimal(6,2)
,[17-Sep] decimal(6,2) 
,[17-Oct] decimal(6,2) 
,[17-Nov] decimal(6,2) 
,[17-Dec] decimal(6,2)
,[18-Jan] decimal(6,2) 
,[18-Feb] decimal(6,2) 
,[18-Mar] decimal(6,2) 
,[18-Apr] decimal(6,2) 
,[18-May] decimal(6,2) 
,[18-Jun] decimal(6,2))

 Insert into test 
 values(597, 0,0,0,84.01,0,0,0,0,0,95.13,0,0)


 select * from test
 Declare @sqlUnpivot as NVarchar(4000)
  Declare @sqlPivot as NVarchar(4000)
Declare @ColsUnpivot as NVarchar(4000)=null
Declare @ColsPivot as NVarchar(4000)=null
 
 
 
Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+','  + QUOTENAME(COLUMN_NAME) +','+  Cast(ORDINAL_POSITION as varchar(3)) +')'
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'

  Select @ColsPivot =  COALESCE(@ColsPivot + ', ', '')  + QUOTENAME(COLUMN_NAME)  
FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE TABLE_NAME='test' and COLUMN_NAME'emp_id'
 
 
Select @sqlUnpivot='Select
emp_id,Col, Nullif(YY_MMM,0) YY_MMM, ORDINAL_POSITION
into temp
FROM test t
CROSS APPLY (Values ' + @ColsUnpivot + '  )  d(Col, YY_MMM,ORDINAL_POSITION) '
exec (@sqlUnpivot)
--print @sqlUnpivot
  
Select @ColsPivot = STUFF( (SELECT  ',' + 'Max(CASE WHEN Col=' + quotename(Col,'''') + ' THEN [YY_MMM] else 0 end ) as ' + quotename(Col,'[')  + char(10)+char(13)
                             FROM temp
							 order by ORDINAL_POSITION
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
  --print @ColumnHeaders
 
Set @sqlPivot  =' Select emp_id,'+   @ColsPivot + ' from 
( SELECT emp_id, Col, ORDINAL_POSITION, 
CAST(SUBSTRING(MAX( CAST(ORDINAL_POSITION AS BINARY(4)) + CAST(Nullif(YY_MMM,0) AS BINARY(8)) )
OVER( ORDER BY ORDINAL_POSITION ASC ROWS UNBOUNDED PRECEDING ),5,8) AS Decimal(6,2) ) [YY_MMM]
FROM temp) t Group by emp_id    ';
     
--print @sqlPivot
EXEC(@sqlPivot)

--clean up temp table
 drop table temp


 drop table test



 

Migrate Data with BCP

  

 
 declare @qrytext varchar(1000), @filepath varchar(1000)
 set @filepath = '  "C:\DATA\mytable_Data.txt" ' 
set @qrytext = '"SELECT   t1.col1,t2.col2,t3.col3 ,t3.col4 from BD1.dbo.t1 t1 JOIN myDB.dbo.t2 t2 on t1.id = t2.oldid JOIN myDB.dbo.t3 p on t1.id2 = t3.oldid "'
--@qrytext should be in one line

------ generate format file and need to remove the first row in the format file to skip a table column
----DECLARE @cmd1 VARCHAR(4000) 
----set @cmd1 ='bcp   [myDB].[dbo].[mytable]  format nul  -f C:\DATA\mytableFormatFile_Raw.fmt  -c -t\t -r\n -T -S '+ @@servername;;
----exec master..xp_cmdshell @cmd1--, NO_OUTPUT

--export data
 DECLARE @cmdOut VARCHAR(4000) 
set @cmdOut ='BCP ' + @qrytext + ' QUERYOUT ' + @filepath + ' -t\t -r\n -c -T -S '+ @@servername;;
exec master..xp_cmdshell @cmdOut, NO_OUTPUT


 --import data
DECLARE @cmdIn VARCHAR(4000) 
set @cmdIn = 'bcp  myDB.dbo.[mytable] IN ' + @filepath + ' -f C:\DATA\mytableFormatFile.fmt -T -S '+ @@servername
exec master..xp_cmdshell @cmdIn, NO_OUTPUT

 ----delay 2 seconds
 WAITFOR DELAY '00:00:02' 

--clean up
DECLARE @cmdDelete VARCHAR(4000) 
SET @cmdDelete ='del '+ @filepath
exec  master..xp_cmdshell @cmdDelete, NO_OUTPUT




 

Bulk Insert Fixed Length Text File

  

 use myDb;

--create destination table structure 
create table bcpTest
(
mob  char(3),
acct  char(5),
mmid  char(4) 
)


 -- Generate fmt format file 
 DECLARE @cmd VARCHAR(4000) 
set @cmd ='bcp  myDb.dbo.bcpTest format nul -c -f E:\DATA\myFormatFiletest.fmt -t ""  -T -S'+ @@servername;;
exec master..xp_cmdshell @cmd


--BULK Insert
BULK INSERT bcpTest
FROM 'E:\DATA\user_data.txt'
WITH
(
FORMATFILE ='E:\DATA\myFormatFiletest.fmt',
ERRORFILE = 'E:\DATA\ERROR_FILE_UD3.log'
)
select * from bcpTest
 

 Drop table bcpTest




 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d93ac67-7499-4614-b4ea-3c81daf64d0f/bcp-width-range?forum=transactsql


Database is in Single_User Mode and How to Change to Multi_User Mode

For unknown reason (due to operation failure), a database was stuck in Single_User mode.

Here are a few steps to kill the troubled session and change the database back to multi_user mode.

–1.Find the session_id to kill
Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID(‘DBName_In_Single_User_Mode’);

–2: Kill the found session_id

Kill theseessionid ;

–3. set the database to multi_user mode
USE [master]

ALTER DATABASE [DBName_In_Single_User_Mode] SET MULTI_USER WITH ROLLBACK IMMEDIATE;


Find All Related (directly or indirectly) IDs

  

DECLARE @Value INT
SET @Value = 6

CREATE TABLE People (ID INT PRIMARY KEY , name NVARCHAR(50), motherID INT, fatherID INT, sex NVARCHAR(50));

INSERT People VALUES(1, 'A', NULL, NULL, 'male');
INSERT People VALUES(2, 'B', NULL, NULL, 'female');
INSERT People VALUES(3, 'C', 1, 2, 'male');
INSERT People VALUES(4, 'X', NULL, NULL, 'male');
INSERT People VALUES(5, 'Y', NULL, NULL, 'female');
INSERT People VALUES(6, 'Z', 5, 4, 'female');
INSERT People VALUES(7, 'T', NULL, NULL, 'female');

CREATE TABLE marriages (
HusbandID INT REFERENCES People(id),
WifeID INT REFERENCES People(id)
)
INSERT marriages VALUES (1,2);
INSERT marriages VALUES (4,5);
INSERT marriages VALUES (1,5);
INSERT marriages VALUES (3,6);

--create source relation dataset
;with dataSource as (
select ID id, null relatedid from People
union
select ID, motherID relatedid from People
union
select ID, fatherID relatedid from People
union
Select HusbandID, WifeId relatedid  from marriages

)

, LeftIDs AS
(
  SELECT id, relatedid  FROM dataSource
  WHERE relatedid = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON a.id = b.relatedid
)
, RightIDs AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN LeftIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
, RightIDs2 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs b ON ( b.relatedid = a.id or a.relatedid = b.id)

)
 , RightIDs3 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs2 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)
 , RightIDs4 AS
(
  SELECT id, relatedid FROM dataSource
  WHERE id  = @Value
    UNION ALL
  SELECT a.id, a.relatedid FROM dataSource a
  JOIN RightIDs3 b ON ( b.relatedid = a.id or a.relatedid = b.id)
)

,finalcte as
(
  SELECT id  FROM LeftIDs
  UNION
  SELECT relatedid  FROM LeftIDs
  UNION
  SELECT id  FROM RightIDs
  UNION
  SELECT relatedid
  FROM RightIDs
  UNION
  SELECT id FROM RightIDs2
  UNION
  SELECT relatedid  FROM RightIDs2
  UNION
  SELECT id  FROM RightIDs3
  UNION
  SELECT relatedid  FROM RightIDs3
  UNION
  SELECT id  FROM RightIDs4
  UNION
  SELECT relatedid FROM RightIDs4

)

select id from finalcte
WHERE id is not null

---brutal force solution
--declare @id int=1
 
 
;with mycte as (
select ID, null relatedID from People
union
select ID, motherID relatedID from People
union
select ID, fatherID relatedID from People
union
Select HusbandID, WifeId relatedID  from marriages
)
 
 
,mycte2 as (
SELECT id  FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
union
SELECT   relatedID FROM mycte
where id in (select id from mycte where id=@id)
or relatedID in (select relatedID from mycte where id=@id)
)
 
 
select id from mycte 
where  id in (select id from mycte2 ) or relatedID in (select id from mycte2)
union
select relatedID from mycte 
where ( id in (select id from mycte2 )or relatedID in (select id from mycte2))
and relatedID is not null
 
 
 
drop table marriages, People




 

https://stackoverflow.com/questions/18601791/need-query-to-select-direct-and-indirect-customerid-IDses


Value Pair Query

DECLARE @TypeMax TABLE
(
MaxCol nvarchar(max)
)

INSERT INTO @TypeMax
VALUES (‘{“ID”:”075405435453.doc”,”Name”:”Scenario1″,”Date”:”2016-12-19 23:01:03″,”Type”:”SL”}’)

, (‘{“ID”:”55453450480003.wav”,”Name”:”Change43″,”Date”:”2016-12-13 21:03:23″,”Type”:”AL”}’)

–SELECT * FROM @TypeMax

–SQL Server 2016 or 2017
SELECT isJSON(MaxCol),
JSON_VALUE(MaxCol, ‘$.ID’) Id,
JSON_VALUE(MaxCol, ‘$.Name’) Name,
JSON_VALUE(MaxCol, ‘$.Date’) [Date],
JSON_VALUE(MaxCol, ‘$.Type’) Type
FROM @TypeMax

–Before JSON functions were introduced to SQL SERVER
—Use XML shredding

;With mycte as (
SELECT Cast(N'<H><r ‘ + Replace(Replace(Replace(Replace(Replace( MaxCol , ‘{“‘,”), ‘}’,”), ‘”,”‘,'” ‘),'”:”‘,’=”‘) + ‘” /></H>’ ,'””‘,'”‘ ) as xml) AS [vals]
FROM @TypeMax
)

,mycte1 as (
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value(‘count(.)’, ‘tinyint’)) rn,
S.a.value(‘@ID’, ‘varchar(50)’) as [ID],
S.a.value(‘@Name’, ‘varchar(50)’) as [Name],
S.a.value(‘@Date’, ‘varchar(50)’) as [Date],
S.a.value(‘@Type’, ‘varchar(50)’) as [Type]

FROM mycte d CROSS APPLY d.[vals].nodes(‘/H/r’) S(a) )

select ID,Name,[Date],[Type] from mycte1
/*
ID Name Date Type
075405435453.doc Scenario1 2016-12-19 23:01:03 SL
55453450480003.wav Change43 2016-12-13 21:03:23 AL
*/

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4f229634-d6bb-4096-809b-544895bb0529/sql-help-one-column-spilt-into-multiple-columns?forum=transactsql