When you have a set of files that you need to load into a database, a For Each Loop in SSIS can help with this process. The first thing to do is drag in a for each loop into the control flow of your package and set the properties of the tasks. Let's say we have some data files in the folder location "C:\DataFiles\", and each file has a ".Dat" extension. Then we set the folder to "C:\datafiles" and the files to "*.dat". Then we need to create a string variable on the package named "strFileName". ...
Read More
If you need to archive a file and want to append the date to the name of the file before the extension then expressions are your answer. You can create a simple expression in the properties of a variable and use that variable in the expressions of the connection in the connection manager. Let's walk through it. First create a variable name strFileName and set the value to "xxxx". This value is irrelevant because the expression is going to overwrite it. To create a variable right click in the con...
When setting up a Source and Destination in SSIS one of the common features to uses is the expressions. This makes your packages dynamic and your connection can update based on programing logic. If this is done incorrectly you will see the error "Non Fatal Errors occurred while saving the package ... the connection is not found. "The expressions need to be placed on the connection in the connection manager, not on the task itself. For Example: Let create a File System Task to move a file and Try...
When writing a custom SSIS task the best practice is to create the UI separate from the control. This will allow the task to run faster during package execution. One of the problems people have when creating a custom SSIS task is, knowing the fully qualified namespace of the task to enter into the UITypeName. Example:Namespace SendMailAdvancedNS <DtsTask(DisplayName:="Send Mail Advanced", _ IconResource:="SendMailAdvancedNS.Letter.ico", _ Description:="Send Mail with Advanced Features", _ UIT...
SSIS For Each ADO Enumerator Loop If you need an SSIS package to run an execute SQL task with a where clause and you need to have a parameter in the where clause traverse through a list of values in a table. In this example we will find the types of Customers in the Adventure works Customer Table and run a “for each loop” with the where clause targeting each distinct customer type in each iteration of the loop. The first task we will create is an execute SQL task that will find the list of accou...
It can be a pain to launch an SSIS package remotely. So I developed a vb.net standalone application to remotely launch a package. It does require the host computer to have SSIS installed. This is just a beginning program. I am going to be adding other features like variables and config files. Eventually it will allow you to run the package remotely where the package will run on the remote machine. But this will probably require creating a web service. Please leave comments, this is my first blog...
If you need to loop through and XML file, or just want to have a loop in an SSIS package loop through a list you have created using a for each nodelist is the task to use. First we create a for each loop and set the collection to node list enumerator. Then we set the document source type to file connection and the document source to our XML file. I suggest using a file instead of direct XML because this allows us to update the package externally and prevents us from having to redeploy the packag...
"Subscriptions cannot be created because the credentials used to run the report are not stored or if a linked report, the link is no longer valid" This is an error that is easily avoided. When deploying a report to the report server we need to ensure that the data source have the credentials stored. Simply click on the data sources link in the report server web interface. Then select the data source that is being used for the report. Change the data source to stored credentials and enter in the ...
I had an interesting question from a student in one of my SSIS classes. If you are calling a package from the DTexec command using the set command to pass in a variable value and the package has a configuration file setting the variable value, which one wins out? I set up a package with a variable named "Test" and the value in the package set to "Package". The value of the configuration file passing in the value to the test variable was set to "Config". I then called the package with a DTexec co...
If you have a Execute SQL command in an SSIS package that has a parameter, you may get no value from the query. This will cause the Execute SQL task to fail if it is looking for a value to pass into the result set variable. To get around this use an If Exist command in the Select SQL statement. Example: You have the following statement in your Execute SQL Task in your package that fails when the product ID is not in the table: Select Style From Production.product Where ProductID = ? If the Produ...