Scheduling the SSIS package which is using SQL Server Authentication to connect to Database

Dear Friends,

There is only a small change required to schedule the SSIS package which is using SQL Server authentication to connect to Database other than Windows authentication (Go through the blog Schedule SSIS through SQL Server Management Studio... to know how to schedule the SSIS package)

Go to step properties (Means Add step or edit step window) -- After selecting the package ,Click on Data sources Tab. There we can see all the connections of SSIS package which we selected. The make the check box "True" for that connection string which is using SQL Server authentication. Then edit the connection string by inserting the SQL server User's password after the User ID

For Eg: Data Source=Databaseservername;User ID=test;pwd=tstpwd;Initial Catalog=testdb;Provider=SQLOLEDB;Persist Security Info=True;

For more clarification refer the following image

Schedule SSIS through SQL Server Management Studio (SQL Server 2005)

Scheduling an SSIS Package is an easy job for a DBA.

1. Select New Job from the pop-up menu. (SQL Server Agent --> Jobs --> New Job)

2. Specify a new for the New Job in the General page. Click on New on the Step page, specify the step name, Select SQL Server Integration services in the Type combo box, Select the File System as Package Source and Select package from the path where the package is stored.

3. Click on the Datasource tab, there we could see all the datasources which we specified in the SSIS package. Check mark on those Datasources and verify those attributes specified in the connection string.

4. There is one more interesting option, we can write the log files into files (Check the logging tab)

after filling all these click OK.

5. To create a schedule for this job go to schedule page and create a Schedule as per our requirement.

I hope this blog will help you to create a scheduled Job For SSIS packages. Please let me know if you have any questions

Migrate DTS to SSIS

Migrating DTS packages from SQL Server 2000 to SQL Server 2005 is an interesting work.

SSIS is a flexible work environment and it is a very good ETL tool. Those who are interested in Datawarehousing technology OR working in Datawarehousing technology, can try this tool. which is a very user friendly ETL tool

Please follow the following steps to Migrate DTS packages.

1. Login to Business Intelligence Studio

2. Create a new SSIS package (Or open an existing SSIS package to Migrate the SSIS package)



3. Click mouse right button on SSIS Package folder from the Solution explorer and select "Migrate DTS 2000 Package" from the Pop Up menu OR Select "Migrate DTS 2000 Package" from Project menu




4. Then the Migration Wizard will open click Next

5. Select the source database, From where we need to migrate DTS Packages then Click next.

6. Choose the destination folder then Click next.

7. Select those DTS Packages, which we need to migrate to SSIS (SQL 2005)

8. Select the Logfile location. The migration logfile will have all the information related to DTS Migration. Even if there are some errors the logfile will have the reason for failing.

9. Confirm the information we had given to process like SQL 2000 Server name and SSIS package location then click Finish to start the migration Process.

10. Migration process

Click the close button once the process has completed.

The Migrated DTS packages will be there in the SSIS Foleder under Solution Explorer.

11. Now Open the Migrated package by double clicking on the package name OR choose Open from the popup menu

12. Verify the connection of the exisitng package. Sometimes there will connections to test files or excel files, in those cases we need to manually copy those files into the specific locations and specify those paths in the the connection strings. In the case of Database connections change the Server names/Userids/passwords then verify the connection.

13. k for the errors, Warnigs and messages from the below tab. If there are errors clear those before building the package.

14. Build the package. (Now the SSIS package is ready to use)

I hope this iformations will help you to migrate the DTS packages. Also let me know if you have any questions or comments.

Have a Great Day.......