Convert String date in flat file to sql date column in SSIS package

See also questions close to this topic

• Keyword IF not expected

  I have the following inline SQL code, running on an AS400. The IF comes up as unexpected but I don't understand why. What I'm trying to do is to build a stored procedure to update, or insert, a table row. The code shown below is inline to ensure my logic is correct.

  I've tried a number of different approaches including IF EXISTS, CASE, SET @COUNT = SELECT COUNT(*). I continue to run into odd errors.

  In the code shown below, I fail when I hit the IF statement.

  CREATE OR REPLACE VARIABLE IN_LOC         CHAR(02);     
  CREATE OR REPLACE VARIABLE IN_MCO         CHAR(02);     
  CREATE OR REPLACE VARIABLE IN_SYMBOL      CHAR(03); 
  CREATE OR REPLACE VARIABLE IN_POLICYNUM   CHAR(07); 
  CREATE OR REPLACE VARIABLE IN_MODULE      CHAR(02); 
  CREATE OR REPLACE VARIABLE IN_RISKLOC     CHAR(05); 
  CREATE OR REPLACE VARIABLE IN_PRCCLASS    VARCHAR(05); 
  CREATE OR REPLACE VARIABLE IN_PRCTER      VARCHAR(03); 
  CREATE OR REPLACE VARIABLE IN_MESSAGE     CHAR(02); 
      
  SET IN_LOC='00';    
  SET IN_MCO='01';    
  SET IN_SYMBOL='CPP';    
  SET IN_POLICYNUM='0003748'; 
  SET IN_MODULE='00'; 
  SET IN_RISKLOC='00003'; 
  SET IN_PRCCLASS='09';   
  SET IN_PRCTER='1';  
  SET IN_MESSAGE='NOT FOUND FOR ODD PROGRAMMING';
  
  UPDATE BASPPRC01
      SET PRCCLASS=IN_PRCCLASS,PRCTER=IN_PRCTER,MESSAGE=IN_MESSAGE
  WHERE CAST(IN_LOC||IN_MCO||IN_SYMBOL||IN_POLICYNUM||IN_MODULE||IN_RISKLOC AS CHAR(21))
      = CAST(LOCATION||MASTERCO||SYMBOL||POLICYNUM||MODULE||RISKLOC AS CHAR(21)) ;
  
  IF @@ROWCOUNT = 0
  BEGIN
      INSERT INTO BASPPRC01
          (LOCATION,MASTERCO,SYMBOL,POLICYNUM,MODULE,RISKLOC,PRCCLASS,PRCTER,MESSAGE)
      VALUES
          (IN_LOC
          , IN_MCO
          , IN_SYMBOL
          , IN_POLICYNUM
          , IN_MODULE
          , IN_RISKLOC
          , IN_PRCCLASS
          , IN_PRCTER
          , IN_MESSAGE ) ;
  END
  
  COMMIT TRANSACTION ;
  
• How to calculate the average score for an entire week and prior week?

  Please check my query:

  SELECT '2020-11-30' AS date,
         AVG(CE.score) AS avg_score
  FROM main_table DF
    JOIN cities A ON DF.CITY_ID = A.CITY_ID
    INNER JOIN score_table CE ON DF.FLIGHT_ID = CE.ID
  WHERE CE.date BETWEEN '2020-11-30' AND '2020-12-07'
  AND   A.TECHNOLOGY IN ('3PU')
  AND   A.STATUS IN ('INSTALLED','IN_PROGRESS')
  AND   A.name_of_city = 'LONDON'
  UNION
  SELECT '2020-12-07' AS date,
         AVG(CE.score) AS avg_score
  FROM main_table DF
    JOIN cities A ON DF.CITY_ID = A.CITY_ID
    INNER JOIN score_table CE ON DF.ID = CE.ID
  WHERE CE.date BETWEEN '2020-12-07' AND '2020-12-14'
  AND   A.TECHNOLOGY IN ('3PU')
  AND   A.STATUS IN ('INSTALLED','IN_PROGRESS')
  AND   A.name_of_city = 'LONDON'
  

  There is a my result:

      date       | avg_score
      2020-11-30 | 86.43
      2020-12-07 | 83.56
  

  The logic is calculation average of score for an entire week and prior week

  I would like to optimize my query
• How to delete some observations in some groups based on a condition

  I would like delete some observations in some groups based on a condition. My condition is each group : if (imput="toto") then delete but if a whole group has imput="toto" then keep it

  data temp;
  input siren $ class $ imput $;
  cards;
  A CP titi
  B CP toto
  C CE tata
  D CE tata
  F CM toto
  G CM toto
  H SU tata
  I SU toto
  ;
  run;
  

  The output I want :

  siren class imput
  A CP titi
  C CE tata
  D CE tata
  F CM toto
  G CM toto
  H SU tata
  

  Many thanks in advance !
• Java: SimpleDateFormat("yyyy-MMM-dd").parse(dateString) throws Exception (unparsable date: "Wed Dec 16 23:48:11 AEDT 2020" )

  I am trying to parse (dow mon dd hh:mm:ss zzz yyyy) format to (yyyy-MM-dd) using java SimpleDateFormat

  Date dt = new SimpleDateFormat("yyyy-MM-dd").parse("Wed Dec 16 23:48:11 AEDT 2020");

  Exception: unparsable date: "Wed Dec 16 23:48:11 AEDT 2020"

  Is there any other way to extract (yyyy-MM-dd)
• How do I reward the user daily?

  I want to reward the user daily in my app, but when the app closes the timer always stops

  I've tried doing this...

   Timer.scheduledTimer(withTimeInterval: 86400, repeats: true) { _ in
  self.points += 100
  }
  

  Can you please tell me how to reward the user daily once?
• Snowflake how to obtain cumulative date values

  Can someone help me with obtaining the result for below logic. I have a table with below columns.

  TYPE    SRC_CURR    TAR_CURR    EX_RATE     EX_RATE_START_DATE  
  M       GBP         USD         1.36687     2/1/2021            
  M       GBP         USD         1.33636     1/1/2021            
  M       GBP         USD         1.32837     12/1/2020           
  M       GBP         USD         1.30242     11/1/2020           
  M       GBP         USD         1.27421     10/1/2020           
  M       GBP         USD         1.31527     9/1/2020            
  ZEU     GBP         USD         1.3654      1/20/2021           
  ZEU     GBP         USD         1.363       1/19/2021           
  ZEU     GBP         USD         1.3587      1/18/2021           
  ZEU     GBP         USD         1.359       1/15/2021           
  ZEU     GBP         USD         1.3689      1/14/2021           
  ZEU     GBP         USD         1.3639      1/13/2021           
  ZEU     GBP         USD         1.3664      1/12/2021           
  ZEU     GBP         USD         1.3518      1/11/2021           
  ZEU     GBP         USD         1.3568      1/8/2021            
  

  So I need to form a new column which is EX_RATE_END_DATE from above values as shown below. Ideally the requirement is to have EX_RATE_END_DATE to max 9999-12-31 by default for the latest start date and for rest of the records it should be previous max start date - 1.

  Please find below the output required,

  TYPE    SRC_CURR    TAR_CURR    EX_RATE     EX_RATE_START_DATE  EX_RATE_END_DATE
  M       GBP         USD         1.36687     2/1/2021            12/31/9999
  M       GBP         USD         1.33636     1/1/2021            1/31/2021
  M       GBP         USD         1.32837     12/1/2020           12/31/2020
  M       GBP         USD         1.30242     11/1/2020           11/30/2020
  M       GBP         USD         1.27421     10/1/2020           10/31/2020
  M       GBP         USD         1.31527     9/1/2020            9/30/2020
  ZEU     GBP         USD         1.3654      1/20/2021           12/31/9999
  ZEU     GBP         USD         1.363       1/19/2021           1/19/2021
  ZEU     GBP         USD         1.3587      1/18/2021           1/18/2021
  ZEU     GBP         USD         1.359       1/15/2021           1/17/2021
  ZEU     GBP         USD         1.3689      1/14/2021           1/14/2021
  ZEU     GBP         USD         1.3639      1/13/2021           1/13/2021
  ZEU     GBP         USD         1.3664      1/12/2021           1/12/2021
  ZEU     GBP         USD         1.3518      1/11/2021           1/11/2021
  ZEU     GBP         USD         1.3568      1/8/2021            1/10/2021
  

  It would be great if someone help me with getting the desired result set by any possible ways in snowflake.
• Using ADO.Net Connection Manager with Parameterized Credentials in SSIS

  I'm working on an SSIS package to extract data from one old database and move to a SQL database. The only way to connect to the source database is using ODBC. I set up an ADO.NET connection manager and project parameters to store the username and password. Then I went to the connection and chose "Parameterize...". I selected username and password and linked to the project parameters I set up. The connection continues to fail. The only way I've been able to get it to work is to allow the package to save sensitive data and to save the password in the connection manager (going to edit, typing in username and password, then saving). If I rely on the parameters it does not work. I also tried deploying to the SSIS catalog, then executing by going to the package and entering the credentials under the Connections Managers tab. The only way I've been able to get this to work is to allow it to save sensitive data, enter credentials directly in the package and then save.

  I cannot figure out why it will not pass the username and password from parameters or with the connection manager when executing in SSMS. The error I get back is that it's missing the password.

  Any help would be greatly appreciated! I'm not new to SSIS, but have tried everything and can't seem to make this one work. I'm stuck using ADO.NET and ODBC.
• know package wise destination table information using sql query

  I have multiple SSIS package so it is hard to know the destination table used in packages. I have to know package wise destination table information using SQL query.

  Column to require in the result PackageName, DestinationTableName.

  Thanks, Nayan.
• SSIS script task errors out when executing from SQL Server Agent Job . System.IO.FileNotFoundException: Could not load file or assembly

  I have SQL Server 2019 installed on a Windows 2019 Virtual machine. Microsoft Visual studio Professional 2012 is installed on that server.

  • I have bunch of SSIS packages in a project, some of those have script task written in C# 2012 and all those run fine from Visual Studio.

  • Any package which has a script task fails when ran from SQL Server Agent job with following errors.

  Description: There was an exception while loading Script Task from XML: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.VisualStudio.Tools.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxx' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.VisualStudio.Tools.Applications, Version=15.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx' at Microsoft.SqlServer.IntegrationServices.VSTA.VstaHelper.b__31_0() at Microsoft.SqlServer.VSTAHosting.VSTAScriptingEngine.DisposeVstaHelper() at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.MigrateVSTADenaliScriptProject(XmlElement elemProj, IDTSInfoEvents events) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

  WRN: Assembly binding logging is turned OFF. To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1. Note: There is some performance penalty associated with assembly bind failure logging. To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog]. End Error Error: 2021-01-21 20:52:01.92 Code: 0x00000003 Source: Create Voucher in ERP Description: The Script Task is corrupted. End Error Error: 2021-01-21 20:52:01.92 Code: 0xC0024107 Source: Create Voucher in ERP Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:52:01 PM Finished: 8:52:01 PM Elapsed: 0.5 seconds

  Some of the thread talked about changing the project property Run64bitruntime to False , didn't work.

  Some threads talked about reinstalling SQL Server and Visual Studio but I really don't want to do that as many of those talks about a lot of other errors pops up after reinstalling.

  Please suggest what can be done to resolve this.

  Thanks