Data Developers

This month I published a series on SQL Shack about the Hadoop components added to SSIS in SQL Server 2016 release. The series is composed of three articles as following: SSIS Hadoop Connection Manager and related tasks In this article, I gave a brief introduction to Hadoop and how it is integrated with SQL Server. Then, I illustrated how to connect to the Hadoop cluster on-premises using the SSIS Hadoop connection manager and the related tasks. Importing and Exporting data using SSIS Hadoop components In this article, I briefly explained the Avro and ORC Big Data file formats. Then, I talked about Hadoop data flow task components and how to use them to import and export data into the Hadoop cluster. Then I compared those Hadoop components with the Hadoop File System Task. Finally, I concluded my work. Connecting to Apache Hive and Apache Pig using SSIS Hadoop components In this article, I talked about Hadoop Hive and Hadoop Pig Tasks. I first gave a brief...

A few months ago, I started writing a new series of articles about Business Intelligence Markup Language (BIML) for the SQL Shack website. After finishing writing about using BIML in SQL Server Integration Services, I would like to share with you these articles: Getting Started With Biml In this article, we will give a brief overview of BIML by showing how it can be installed and how we can use it to generate SSIS packages using the BimlExpress tool. Finally, we will provide some useful resources to learn this technology. Converting SSIS packages to Biml scripts In this article, we will explain how to generate scripts from existing SSIS packages by showing all related options. Then, we will analyze the generated script and identify how each object is mentioned in it. Using Biml scripts to generate SSIS packages In this article, we will explain how to create SSIS packages using business intelligence markup language by providing a step-by-step guide where we will il...

SQL Server Integration Services provides a wide variety of features that helps developers to build a robust Extract, Transform and Load process. After many years contributing to SSIS related tags on Stackoverflow.com, I can say that many developers have some misunderstanding about SSIS features (SSIS OLE DB Source, SSIS Expressions, SQL Server destination …) especially those which are very similar and have some common usability. Last year, I started writing SQL related articles for the SQL Shack blog. In this post, I would like to share with you a series I have published a few months ago called "SSIS features face-to-face" which is composed of 11 articles where I made a comparison between similar SSIS features. SSIS OLE DB Source: SQL Command vs Table or View In this article, we do a comparison between SSIS OLE DB Source data access modes:  “Table or View”  with Specific columns selected Vs. Using  “SQL Command”  to select specific columns. SSIS Ex...

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...