Data Developers

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 am using SSIS 2016. I need to execute a stored procedure that returns 4 result sets. I only need to keep the first result set and write this to a table. I can not modify the stored procedure. I do not care about any of the data returned in the other result sets. The stored procedure is in a SQL Server 2016 database. Results will also reside in SQL Server 2016. I currently have this process running in SSIS 2008 using the "SQL Command" data access mode in an OLE DB Source like below. I have this in a For Each Loop Container to pass a series of param values to the stored procedure as I execute it multiple times for different param values on a daily basis. SET FMTONLY OFF ; EXEC myProc @ Param1 = ?, @ Param2 =?, @ Param3 = ?; By default SSIS 2008 is only returning the first result set, which has worked for me as I only care about the first result set. I am using the Native OLEDB SQL Server client. From what I have read, it has changed t...

Problem: I need to change a stored procedure that feeds an SSIS package. Comparing the old & new with sp_describe_first_result_set, the only change is one field's "is_updateable". So, does that changing mean I need to do a metadata refresh against the SSIS project? TIA Solution TL DR: If you need to use the newly added column in the Data integration process then you have to open the package and refresh the OLE DB Source metadata, else the package will still work as excepted. Details: I created a Table  Table_1  with the following columns: CREATE TABLE [ dbo ].[ Table_1 ]( [ ID ] [ int ] NOT NULL , [ Col1 ] [ nchar ]( 10 ) NULL , [ Col2 ] [ nchar ]( 10 ) NULL , [ Col3 ] [ nchar ]( 10 ) NULL , CONSTRAINT [ PK_Table_1 ] PRIMARY KEY CLUSTERED ( [ ID ] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) O...