It is the first time I write about the SSIS (SQL Server Integration Services) package. Previously, in the older version of SQL Server, it was known as Data Transformation Services (DTS) in BIDS (Business Intelligence Development Studio). The SSIS was introduced in SQL Server 2005 to perform extraction, transformation and loading of data. Back then, this service was available for standard and enterprise version. Now, in the Visual Studio 2019, you may notice the “New Project” does not include the SSIS. However, you can download from the Visual Studio through the Manage Extensions. When the Manage Extensions window loads, type “Integration Services” in the search box. Alternatively, you can go to the marketplace to download the installer.
With Visual Studio 2019, the required functionality to enable Analysis Services, Integration Services, and Reporting Services projects has moved into the respective Visual Studio (VSIX) extensions only.
I started this post because I faced a situation whereby my column has changed its data type in the OLE DB Source node without knowing it. It happened when I was updating the datamart columns with the new extraction criteria, and I was working on some columns with the date and date-time format.
I found out the change from date format to date-time format when the generated CSV file consisted of data with timestamp values. I tried to cast the column to date format or even converted it to a string; still, it returned as a timestamp format. I checked the SQL query that extracted the data from the data mart, and it cleared enough that the SQL query did not return timestamp values.
So, where did it go wrong?
The change was not initiated by myself, and I am clueless how did it get changed in the first place. After investigated, I realized the column changed to the timestamp after the SQL command text executed in OLE DB Source node.
Then, I googled and found that I have to check the metadata in the Data Flow Path Editor (the grey arrow) that connects to the next node. Maybe, if you do not explore so much on each node and arrow in the SSIS package, you probably will not know there is this metadata properties box. In the properties dialogue, it has a tab called metadata where it shows the column name, the data type, and others. Here, I found that the column has a timestamp data type. I wanted to edit the row of the column that caused the issue. However, I cannot make a direct data type change from this properties dialogue.
How did I fix it?
What I did was removing the affected column(s) from the SQL command text and redrew the arrow to the next node.
From here, I noticed that the affected column wholly removed from the metadata. Next, I went into the SQL command text again, and replace the query with the column that I removed earlier. Then, I checked back in the metadata, and the column showed the correct data type, the date format. Maybe, many of us already know how to deal with this error, and I do not find my solution is a good one. Am wondering if there is another workaround or better way to resolve metadata changes in SSIS package?