January 15, 2015 at 9:25 pm
Lempster (1/15/2015)
Jeff Moden (1/14/2015)
We don't even use the "Maintenance Plan" bit of SSIS available in the Object Explorer of SSMS.I'm with you on that one Jeff, but I have to disagree with you in general about SSIS; I've found it to be incredibly powerful. It's all about using the right tool for the right job and it surely can't hurt to broaden one's knowledge of the SQL Server suite?
Regards
Lempster
Shifting gears on this same subject, let's have some fun and do a cooperative exploratory on a very typical spreadsheet import problem.
I've attached a spreadsheet (Sample Spreadsheet for Import 20140301.xlsx). Import it with SSIS with the understanding that the "category" columns (labeled Phy, Mat, Chem) for the next month can be added (Hist could be added, and only for the new month, for example) or deleted (Mat could be deleted for the new month, for example), that there will be a next month, and additional rows can be added at any time and with the final understanding that as the spreadsheet grows, you're not allowed to make any changes to the SSIS package but it must still handle the changes that I've outlined. Bonus points if the only parameter required is the file name to import.
When the next month occurs (attached as Sample Spreadsheet for Import 20140401.xlsx), the data is successfully imported into the original table without any changes to the SSIS package or the spreadsheets themselves.
Use as many tools as you need to get the job done.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2015 at 9:29 pm
Samuel Vella (1/15/2015)
Apologies for the delay - a combination of Laptop battery dying, intermittent mobile internet coverage and Visual Studio not liking my Virtual Machine 🙁Two packages built
One which loads via conventional SSIS methods, the other via c# (embedded in a package as requested)
Both append the file name to each row
Both exclude any files which already exist in the load table.
The packages can be downloaded from here:
https://dl.dropboxusercontent.com/u/1630056/CSFileLoadDemo.dtsx
https://dl.dropboxusercontent.com/u/1630056/SSISFileLoadDemo.dts
Sample file is here:
https://dl.dropboxusercontent.com/u/1630056/Import1.txt
They are minimally tested and thrown together. The C# especially should not be used as an example of how to code C#.
Script to create the destination table is here:
CREATE TABLE [dbo].[SampleFileStaging]([col1] [int] NULL,
[col2] [varchar](50) NULL,
[FileName] [varchar](100) NULL
) ON [PRIMARY]
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2015 at 9:50 pm
Polymorphist (1/9/2015)
Folks i'm dead in the water here - can anyone help at all? Nothing I try works and I have a feeling i'm overlooking something very simple.
Forgive me if this is insulting, but did you check the column mapping in the destination and make sure your derived column is being assigned to the filename column?
If you added the derived column transform later and/or the column the name does not exactly the table's column, it may not have done it for you automatically.
January 16, 2015 at 12:45 am
Jeff Moden (1/15/2015)
Samuel Vella (1/15/2015)
Apologies for the delay - a combination of Laptop battery dying, intermittent mobile internet coverage and Visual Studio not liking my Virtual Machine 🙁Two packages built
One which loads via conventional SSIS methods, the other via c# (embedded in a package as requested)
Both append the file name to each row
Both exclude any files which already exist in the load table.
The packages can be downloaded from here:
https://dl.dropboxusercontent.com/u/1630056/CSFileLoadDemo.dtsx
https://dl.dropboxusercontent.com/u/1630056/SSISFileLoadDemo.dts
Sample file is here:
https://dl.dropboxusercontent.com/u/1630056/Import1.txt
They are minimally tested and thrown together. The C# especially should not be used as an example of how to code C#.
Script to create the destination table is here:
CREATE TABLE [dbo].[SampleFileStaging]([col1] [int] NULL,
[col2] [varchar](50) NULL,
[FileName] [varchar](100) NULL
) ON [PRIMARY]
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
Here's the T-SQL solution that does the same thing. The use of xp_CmdShell would actually make this easier (and that's what I'd normally use) but I kept it down to just using xp_DirTree.
First, here's the same destination table that you had except I added a named constraint as a default for the filename and made the column NOT NULL.
CREATE TABLE dbo.SampleFileStaging
(
col1 INT NULL,
col2 VARCHAR(50) NULL,
[FileName] VARCHAR(100) NOT NULL CONSTRAINT DF_SampleFileStaging_FileName DEFAULT ''
)
;
Next, I made a "loader" view to use instead of a BCP format file.
CREATE VIEW dbo.SampleFileStagingLoader AS
SELECT Col1, Col2
FROM dbo.SampleFileStaging
;
Then, this stored procedure does the same thing as what you said your package does. You just pass it a path name and a file name filter.
CREATE PROCEDURE dbo.ImportToSampleFileStaging
/**********************************************************************************************************************
Purpose:
Given a file path and a file name filter, import the found files into the dbo.SampleFileStagingLoader table.
Of course, the files should be in the proper format to be imported into the table.
Usage:
--===== Basic syntax
EXEC dbo.ImportToSampleFileStaging @pImportFilePath, @pImportFileFilter
;
--===== Import all files from the C:\Temp\ direcctory that have a file name that starts with "IMPORT", followed by
-- at least one numeric digit, that has a .TXT extension.
EXEC dbo.ImportToSampleFileStaging 'C:\Temp\','Import[0-9]%.txt'
;
Revision History:
Rev 00 - 16 Jan 2015 - Jeff Moden
- Initial creation for http://www.sqlservercentral.com/Forums/Topic1649977-364-1.aspx
**********************************************************************************************************************/
--===== Parameters for this stored proceddure
@pImportFilePath VARCHAR(500)
,@pImportFileFilter VARCHAR(100)
AS
--=====================================================================================================================
-- Environment
--=====================================================================================================================
--===== Suppress the auto-display of rowcounts for appearance and performance.
SET NOCOUNT ON
;
--===== Delouse the input parameters for SQL and DOS Injection.
-- Return no clues if present.
IF @pImportFilePath LIKE '%[^-a-zA-Z0-9:.\__]%' ESCAPE '_'
OR @pImportFileFilter LIKE '%[^-a-zA-Z0-9:.\__%[]]%' ESCAPE '_'
RETURN
;
--=====================================================================================================================
-- Temp Tables
--=====================================================================================================================
--===== Create a table to store all the file names.
IF OBJECT_ID('tempdb..#AllFiles','U') IS NOT NULL
DROP TABLE #AllFiles
;
CREATE TABLE #AllFiles
(
SubDirectory VARCHAR(100)
,Depth INT
,IsFile BIT
)
;
--===== Create a table to store the filtered file names to import.
IF OBJECT_ID('tempdb..#ImportFileName','U') IS NOT NULL
DROP TABLE #ImportFileName
;
CREATE TABLE #ImportFileName
(
FileNumber INT IDENTITY(1,1)
,ImportFileName VARCHAR(100)
)
;
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Local variables
DECLARE @Counter INT
,@FileCount INT
,@ImportFileName VARCHAR(100)
,@SQL VARCHAR(MAX)
;
--=====================================================================================================================
-- Get the names of the files to import
--=====================================================================================================================
--===== Get all the file names for the given source directory.
INSERT INTO #AllFiles
(SubDirectory,Depth,IsFile)
EXEC xp_DirTree @pImportFilePath,1,1
;
--===== Get the filtered file names to import and remember how many there are.
INSERT INTO #ImportFileName
(ImportFileName)
SELECT ImportFileName = SubDirectory
FROM #AllFiles af
WHERE SubDirectory LIKE @pImportFileFilter
AND NOT EXISTS (SELECT * FROM dbo.SampleFileStaging WHERE [FileName] LIKE '%'+af.SubDirectory+'%')
ORDER BY SubDirectory
;
SELECT @FileCount = @@ROWCOUNT
;
--=====================================================================================================================
-- Import each file and the name of the file
--=====================================================================================================================
SELECT @Counter = 1
;
WHILE @Counter <= @FileCount
BEGIN
SELECT @ImportFileName = ImportFileName
FROM #ImportFileName
WHERE FileNumber = @Counter
;
SELECT @SQL = REPLACE(REPLACE(REPLACE('
--===== Replace the default constraint to pickup the file name
ALTER TABLE SampleFileStaging
DROP CONSTRAINT DF_SampleFileStaging_FileName
;
ALTER TABLE SampleFileStaging
ADD CONSTRAINT DF_SampleFileStaging_FileName DEFAULT "<<@pImportFilePath>><<@ImportFileName>>" FOR [FileName]
;
--===== Import the data from the given file path and the found file name.
BULK INSERT dbo.SampleFileStagingLoader
FROM "<<@pImportFilePath>><<@ImportFileName>>"
WITH (
CODEPAGE = "RAW"
,FIRSTROW = 2
,FIELDTERMINATOR = ","
,ROWTERMINATOR = ""
,TABLOCK
)
;'
,'"' ,'''')
,'<<@pImportFilePath>>',@pImportFilePath)
,'<<@ImportFileName>>' ,@ImportFileName)
;
EXEC (@SQL)
;
SELECT @Counter = @Counter + 1
;
END
;
GO
Assuming that you've copied the 3 attached files to your C:\Temp\ directory, you then execute the following and Bob's your uncle.
EXEC dbo.ImportToSampleFileStaging 'C:\Temp\','Import[0-9]%.txt';
It's real easy to copy this from the Dev to QA to UAT to Prod boxes. I just run the script that created the destination table, the view, and the proc.
Since I have this code as a "boiler plate" template, it takes about 5 minutes to make a copy, do a couple of changes for the given destination table, and I'm done.
Admittedly, this was a simple example but much more complex things can easily be done in T-SQL, especially if I break out the xp_CmdShell hammer. For example, I wrote two world wide call accounting packages to download from multiple "PollCat" devices using various connection methods to download from different company's telephone systems and switches and it was all done using methods similar to this and a little help from some DOS batch files that I'd call with xp_CmdShell.
The cool part about this is that it all works from SQL Express (MSDE at the time) so that I can have multiple inexpensive ETL "bricks" downloading simultaneously. In those cases, the staging table was a temp table so that I could run many concurrent jobs at the same time. (I also used BCP format files instead of a "loader" view for those because the data required us to reject certain columns). I also had it move process files to an archive area.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 2:10 am
Jeff Moden (1/15/2015)
Shifting gears on this same subject, let's have some fun and do a cooperative exploratory on a very typical spreadsheet import problem.I've attached a spreadsheet (Sample Spreadsheet for Import 20140301.xlsx). Import it with SSIS with the understanding that the "category" columns (labeled Phy, Mat, Chem) for the next month can be added (Hist could be added, and only for the new month, for example) or deleted (Mat could be deleted for the new month, for example), that there will be a next month, and additional rows can be added at any time and with the final understanding that as the spreadsheet grows, you're not allowed to make any changes to the SSIS package but it must still handle the changes that I've outlined. Bonus points if the only parameter required is the file name to import.
When the next month occurs (attached as Sample Spreadsheet for Import 20140401.xlsx), the data is successfully imported into the original table without any changes to the SSIS package or the spreadsheets themselves.
Use as many tools as you need to get the job done.
Jeff, I've previously done something very similar to this using SSIS (called from a stored procedure) that handles Excel workbooks with a differing number of worksheets whose names can vary from one workbook to another. It can also handle any version of Excel. I will see if I can dig that out - might take me a while!
Chapeau to you for your solution by the way - you are a T-SQL god!
Regards
Lempster
January 16, 2015 at 5:23 am
Jeff Moden (1/15/2015)
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
Hi Jeff
Link has been fixed.. it was missing the last "x" from dtsx
Thanks for your T-SQL method
On the subject of T-SQL vs SSIS
SSIS shines when you have a lot of data outside of a database which needs manipulation before you load it into tables. That data could be manipulated in the database but that means using more disk space and making multiple passes at the data. SSIS gives a read once, write once multi-threaded portable solution
January 16, 2015 at 9:30 am
Samuel Vella (1/16/2015)
On the subject of T-SQL vs SSIS
SSIS shines when you have a lot of data outside of a database which needs manipulation before you load it into tables. That data could be manipulated in the database but that means using more disk space and making multiple passes at the data. SSIS gives a read once, write once multi-threaded portable solution
Understood but it also means that you would need to have a separate server if you truly want to avoid resource contention and that would be extra hardware/licensing/security, etc, costs. There are also a whole lot of things that can't actually be done in SSIS without either calling a script or calling a stored procedure which, in the past, I've seen create a sort of "tower of babble" requiring knowledge of many different languages and/or tools just in one package. Of course, that might also be because the people using it didn't have a clue but I don't know that.
As for multi-threading, it's simple to run multiple jobs on SQL Server to import data in a parallel fashion.
Also, I question the word "portable" here. Admittedly, my experience in SSIS is practically nil but I have seen what some folks have gone through to move packages from Dev to Staging to Prod or simply another server. I may have just seen the wrong parts or someone that didn't know what they were doing, though. What is the process for copying packages from one box to another? Obviously, for the stuff I did, it would just be copying the code for 3 SQL objects (destination table, view, stored procedure) and that could easily be done in a single deployment script.
Just to reassure, I'm not trying to be difficult or argumentative here. I just don't see or understand the draw that SSIS has and I've seen a whole lot of people on this forum and in real life complain about it, a lot. I've also seen several articles on how to do things in SSIS and it seems overly complicated compared to what can be done with a bit of thoughtful T-SQL. I've certainly not needed to use SSIS (and have created some serious ETL systems without it) so I'm only "qualified" to complain about SSIS at the 100,000 foot level, possibly with a great misunderstanding on my part, and so I'm trying to understand the draw that many have.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 9:35 am
Lempster (1/16/2015)
Jeff Moden (1/15/2015)
Shifting gears on this same subject, let's have some fun and do a cooperative exploratory on a very typical spreadsheet import problem.I've attached a spreadsheet (Sample Spreadsheet for Import 20140301.xlsx). Import it with SSIS with the understanding that the "category" columns (labeled Phy, Mat, Chem) for the next month can be added (Hist could be added, and only for the new month, for example) or deleted (Mat could be deleted for the new month, for example), that there will be a next month, and additional rows can be added at any time and with the final understanding that as the spreadsheet grows, you're not allowed to make any changes to the SSIS package but it must still handle the changes that I've outlined. Bonus points if the only parameter required is the file name to import.
When the next month occurs (attached as Sample Spreadsheet for Import 20140401.xlsx), the data is successfully imported into the original table without any changes to the SSIS package or the spreadsheets themselves.
Use as many tools as you need to get the job done.
Jeff, I've previously done something very similar to this using SSIS (called from a stored procedure) that handles Excel workbooks with a differing number of worksheets whose names can vary from one workbook to another. It can also handle any version of Excel. I will see if I can dig that out - might take me a while!
Chapeau to you for your solution by the way - you are a T-SQL god!
Regards
Lempster
Thanks, Lempster. And, to be sure, this isn't a personal confrontation I'm trying to make here. I'm mostly ignorant of SSIS simply because I don't use it. It would really be interesting to see what's necessary to handle an "ever-changing" spreadsheet like the two examples I posted because it's a really common task. It might (heh... no guarantees) help me see the proverbial light on SSIS if such a thing turned out to be incredibly simple in SSIS. It's pretty simple in T-SQL. I'm actually in the process of writing an article on it but I'll post the code for it sometime over the weekend.
Heh... and, no. Not a "god" at this stuff (although I'm humbled by your compliment. Thank you). I'm just a "Joe-bag-o-donuts" that has had to survive the whims of many companies and restrictions. I've found that there are more companies than not that don't have and won't allow SSIS on their systems especially after the round where they had to convert all of their DTS packages to SSIS. To work for such companies, I've had to come up with a trick or two. Anyone could do it if their feet were held to the fire as mine have been. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2015 at 9:47 am
Samuel Vella (1/16/2015)
Jeff Moden (1/15/2015)
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
Hi Jeff
Link has been fixed.. it was missing the last "x" from dtsx
Ah... almost forgot. Like I said, I'm a rank beginner when it comes to SSIS. I have it installed on my laptop just because I'm one of those folks that "installs everything, just in case" but I don't have a clue on how to load either of your packages. Could you help there so that I can take a look. And I really apologize for my ignorance here.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2015 at 4:38 am
Jeff Moden (1/16/2015)
Samuel Vella (1/16/2015)
Jeff Moden (1/15/2015)
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
Hi Jeff
Link has been fixed.. it was missing the last "x" from dtsx
Ah... almost forgot. Like I said, I'm a rank beginner when it comes to SSIS. I have it installed on my laptop just because I'm one of those folks that "installs everything, just in case" but I don't have a clue on how to load either of your packages. Could you help there so that I can take a look. And I really apologize for my ignorance here.
Quick suggestion,Stairway to Integration Services[/url]
😎
January 17, 2015 at 7:31 am
Eirikur Eiriksson (1/17/2015)
Jeff Moden (1/16/2015)
Samuel Vella (1/16/2015)
Jeff Moden (1/15/2015)
No problem. Thank you very much for your efforts.
I have to admit that when it comes to SSIS, I get totally lost because I don't use it. How to I load your packages and run them?
Also, I get a 404 error when I try to download the file with the .dts extension.
Hi Jeff
Link has been fixed.. it was missing the last "x" from dtsx
Ah... almost forgot. Like I said, I'm a rank beginner when it comes to SSIS. I have it installed on my laptop just because I'm one of those folks that "installs everything, just in case" but I don't have a clue on how to load either of your packages. Could you help there so that I can take a look. And I really apologize for my ignorance here.
Quick suggestion,Stairway to Integration Services[/url]
😎
Thanks, Eirikur. I've started reading that stairway in the past and stopped because it wasn't something that I actually needed.
To help wet my appetite for reading that stairway further, would someone please just tell me how to load the previously identified packages so that I might have a look? Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2015 at 8:06 am
Jeff Moden (1/17/2015)
To help wet my appetite for reading that stairway further, would someone please just tell me how to load the previously identified packages so that I might have a look? Thanks.
A quick guide, open BIDS 2008 and create a new Integration Services Project, then select the Solution Explorer, locate the Packages folder, right click and select Import->Existing Package, select From File, browse to the folder and select the package to import.
If you only have BIDS 2005, then first open the package file in a text editor and change the first line of the XML from
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">
to
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.1">.
This way you can open an 2008 package in 2005, most likely will not run without editing but at least it opens.
😎
January 17, 2015 at 11:01 am
Eirikur Eiriksson (1/17/2015)
Jeff Moden (1/17/2015)
To help wet my appetite for reading that stairway further, would someone please just tell me how to load the previously identified packages so that I might have a look? Thanks.A quick guide, open BIDS 2008 and create a new Integration Services Project, then select the Solution Explorer, locate the Packages folder, right click and select Import->Existing Package, select From File, browse to the folder and select the package to import.
If you only have BIDS 2005, then first open the package file in a text editor and change the first line of the XML from
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.2">to
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="SSIS.Package.1">.This way you can open an 2008 package in 2005, most likely will not run without editing but at least it opens.
😎
Excellent. I'll give that a try. Thank you, Eiriker.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2015 at 2:51 pm
Jeff Moden (1/16/2015)
Also, I question the word "portable" here. Admittedly, my experience in SSIS is practically nil but I have seen what some folks have gone through to move packages from Dev to Staging to Prod or simply another server. I may have just seen the wrong parts or someone that didn't know what they were doing, though. What is the process for copying packages from one box to another? Obviously, for the stuff I did, it would just be copying the code for 3 SQL objects (destination table, view, stored procedure) and that could easily be done in a single deployment script.
I'd almost finished a lengthy reply when my laptop crashed and restarted. :angry: I won't repeat everything I'd written, but prior to SQL Server 2012 the method that was widely used to move/copy packages between Prod, Dev, Test etc. was to copy the .dtsx file at the filesystem level and also make any necessary changes to the package Configuration (assuming there was one). Configurations could be XML files, Windows environment variables, SQL Server and a couple of other types or a combination.
With SQL Server 2012, the deployment model changed from package-based to project-based (although it is possible to change between the two). Project-based deployment is more akin to the way that application code has typically been deployed, i.e. the entire project is redeployed even if only one package has changed. The introduction of Environments and Parameters allows things such as Connection Managers (data source connection strings) to take on different values depending on the Environment selected. So, you might have set up Environments called DEV and PROD and you'd select the relevant Environment depending on where you were deploying your project to. It does mean that there is upfront work to configure all your Environment settings, but once that's done, deploying to different servers is simple. I'll freely admit though, it is not as simply as simply creating a set of stored procedures in the relevant database!
Thanks, Lempster. And, to be sure, this isn't a personal confrontation I'm trying to make here.
And it's certainly not taken as such. I'm all for having a healthy debate. Like I said previously it's about using the right tool for the right job and if I was simply importing/exporting data from one place to another without transforming it (EL as opposed to ETL), I would use stored procedures or bcp rather than SSIS too.
I've found that there are more companies than not that don't have and won't allow SSIS on their systems especially after the round where they had to convert all of their DTS packages to SSIS.
.
I guess I've been lucky in that regard, but I agree, converting DTS to SSIS can be a PITA (there are 3rd party utilities to help), but SSIS is a vast improvement on DTS.
One of the things I like about SSIS is the ability to set breakpoints and Data Viewers to that one can interrogate the value of variables and see data as it flows through from one component to another. It can make troubleshooting a lot easier. I realize that similar things can be done in T-SQL, but not without extra coding or using temporary/staging tables.
Out of interest, how would you handle in T-SQL the need to import/export data between different RDBMS platforms (e.g. DB2 -> SQL Server, SQL Server -> Oracle) without having to use intermediate flat files? Linked servers?
I was going to mention Andy Leonard's Stairway series, but I see Erikur has already done that. It's worth revisiting it if only to get concrete proof that your T-SQL methods are better! 🙂
Regards
Lempster
January 17, 2015 at 5:56 pm
@Lemptster,
Outstanding! Thank you for the time you took on that. I especially appreciate the methods you talked about for the promotion of packages and projects.
Shifting to the subject of "EL" v.s. "ETL" and to answer your questions...
That single subject may be the reason why I've found no particular use for SSIS. Most of the companies that I've worked for haven't allowed direct communication between servers or server-to-server communications through applications (and, SSIS is an application). As a result, nearly all of the data that I've had to import or export has been in the form of one text file or another or via (ugh!) spreadsheets.
There have been times for me (working in such a place now) where server-to-server communications have been allowed and neither DTS (in the early days) or SSIS (later on, of course) were allowed by the companies because they didn't want to take the time to set it up, expand their security footprint, or increase what they've called the "tower of babel" especially since it's "known" that both typically require scripting in another language (I've reworked a couple of major DTS packages that used Perl, VBS, calls to Active-X, SQL scripts, and calls to stored procedures all in the same package. The direction given for the rework was to change it all to calls to stored procedures.). I don't know enough about SSIS to say it's different in that area or not but the perception of many companies is that not much has changed in that area.
Sidebar: The Enterprise Architect at my current job forbids the implementation of SSIS because of how many times it "bit" him. His words, not mine. Someday, I'll get a couple of beers in him and press for the details. I've also worked for companies that have asked me for help to dismantle SSIS for various reasons mostly including those I've already spoken of.
To answer your other question, what ends up happening in such cases is the use of Linked Servers is approved and nightly jobs to get the data are done. As bad as that can sometimes be (and I usually am able to dramatically improve the performance by working with the folks at the data source. DB2, in this case.), it's deemed by these companies to be the more attractive method compared to SSIS.
To be honest, none of that has been particularly painful for me since one of my fortes is the import and processing of very large and sometimes seriously complicated "flat" files and some "not so flat" files that can even contain multiple segments/formats in the same file (although I can see how SSIS might be a big help in that area). It also helps immensely to be a bit of an iconoclast when it comes to the use of certain features available in T-SQL. 😛
Shifting gears and as you've mentioned, I have seen the use of breakpoints and Data Viewers by some folks using SSIS. That's impressive and quite handy. As you say, the alternative is the use of temporary/staging tables but that's not required me to do "extra" coding because I never import directly to the final target table. I always use staging tables of one sort or another even if it's a wholesale replacement of a table.
None of that withstanding, I'll admit that this thread has rewet my interest in learning how to use SSIS. Thank all of you for that.
I'd still be interested in seeing what anyone has done to solve a problem in SSIS like the spreadsheet examples I posted. If that turns out to be simpler than I think, that would be strong impetus for me to learn more about SSIS sooner than later.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply