Data Developers

You can use Execute SQL Task to query a Microsoft Access Database, but you cannot use a Truncate command since it is not supported, you have to use a  DELETE FROM ...  command. The following screenshots show an example of the OLE DB Connection manager, and the Execute SQL Task configuration: Original post:  https://stackoverflow.com/questions/58306930/ssis-access-database-need-to-empty-table-on-my-destination-access-database-tab/58348096#58348096

Problem: Have a large number of (TSV) files that need to be imported (periodically) (via SSIS package) into existing MSSQL DB tables. Getting many data type issues from the  OLE DB Destination  tasks eg: [Flat File Source [2]] Error: Data conversion failed. The data conversion for column "PRC_ID" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". and the type suggestions from the connection managers for each table from  Flat File Source  tasks are not accurate enough to prevent errors when running the import package (and the DB types are the correct ones, so don't want to just make them all (wrongly) strings for the sake of loading the TSVs). Is there a way to load the type data for the columns in some single file rather than one by one in the connection manager window for the  Flat File Source  tasks (this would be hugely inconvenient as each table may have many fields)? I have th...

Problem: I am working on an SSIS data flow as shown in the image below. Here are the details of the flow. Getting some records. Adding a dummy column which is a DT_NTEXT type This an OLE DB command which is executing a stored procedure. The output of the stored procedure is XML but is of type NVARCHAR(MAX). The output is populating the dummy field. Writing the XML from the dummy column to a table. When the package is executed, the destination DB only gets populated with a < instead of the full XML. If I change the dummy column to type WSTR, the XML is succesfully written to the table in full. I need to write the XML to an NVARCHAR(MAX) field, as the XML could be large and exceed the limits of the WSTR type. Does anyone have an idea what is going on and how I can write my XML to an NVARCHAR(MAX) field? Solution: After running many experiments and searching over the internet, it looks like this is an issue in SSIS, since OLE DB Command cannot be mapped to  DT_N...

Problem: I would like to implement the following logic to my Lookup query as an expression: SELECT ID , CASE @[ User :: ConnectionStringID ] WHEN 1 THEN "NE" WHEN 2 THEN "BALT" WHEN 3 THEN "NY" WHEN 4 THEN "PA" END AS Region FROM dbo . Table ; My package is executed in a loop where the connection string is dynamically updated using a config file and it loops 4 times as shown in the above logic. I realized today that the Lookup transformation has no expressions available to it and I haven't been able to find another suitable solution. Has anyone found a way around this or a custom transformation script? Solution: Lookup control has expressions, but cannot accessed directly from it just go to your  Dataflowtask  properties and access the expressions like shown in the image below Original Post:  https://stackoverflow.com/questions/42032542/ssis-dynamic-l...

Problem: This question  mentions two libraries, both of which aren't maintained and one has broken links to the source and documentation. SSISUnit  was last updated in 2008 and  SSIStester  has broken links in the documentation and hasn't been updated since 2013. The answers on  social.msdn.microsoft.com  also generally point to one of those two libraries, or some sort of custom solution. Are there any other options? Solution: The most Basic way to perform a SSIS Unit Testing is to create your own testing package. Example below: BUILDING AN SSIS TESTING FRAMEWORK The Most popular Tools to perform SSIS Unit Testing are the ones you listed: SSISUnit SSISTester But after making a deep search i found a new way that is  BizUnit .  BizUnit Framework  which is predominantly used for the Biz Unit testing can be customized to test SSIS Package as well. More info in the link below SSIS:Automated Unit Testing Also if you me...

Problem: I am trying to load some data from csv file to my SQL server. There is a column for Date which has data type Unicode (WSTR) data type in csv file and the column for storing date in SQL server is of Datetime data type. When I used DATA CONVERSION transformation to convert WSTR data to DBTIMESTAMP data, it got changed but with an error that it interchanged the month and date which gives me the wrong date. Like Date should be like 2019-09-03 (For 3rd Sep 2019), but it gives me 2019-03-09. Please suggest what's the issue i am facing? Solution: Problem cause This may occurs when converting a string to a date value without specifying the date format. Reffering to the  SSIS data conversion transformation official documentation : If you are converting data to a date or a datetime data type, the date in the output column is in the ISO format, although the locale preference may specify a different format. Assume that the data is stored in the csv file with the...

Problem: I have a Report which displays 6-groups(5-Rows in each Group) of information. The report is having some filter which can be modified by user. When first time report get loads without any filter its page rendering in report viewer and Print page is perfect, But when filters been added in Report information its get unusual. Could anyone please suggest anything to fix number of rows/groups in each page? Solution: I will provide two methods to solve the issue (1) Try to prevent the text boxes from expanding I will assume that you are showing the report parameters  (filters)  as mentioned in the following link: Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services Then, you should set  CanGrow  property to  False  from the Textbox properties pane as mentioned in folowwing article: Allow a Text Box to Grow or Shrink (Report Builder and SSRS) Also make sure that  Allow height to increa...