SSIS: Don't ask me why!


SQL Server 2019 (1.5G of install)
Visual Studio 2019 (9G of install)

Install it on wifi! :)

1. Documentation seem to indicate SSIS is installed along with visual studio 2019

2. However a "new project" doesn't show SSIS as a project option

3. It appears one has to use "extension manager" in vs to download again!!! wtf?? another 500MB of download on verizon bandwidth!!

4. So I did it anyway. it kicks me to browser to download it leaving aside the vs (wow! who designed this? Certainly a comity)

5. Then it seem to complain some strange processes are running for installation to proceed.

6. I closed vs. still issues. then i have to close "ie" the very browser that started this

7. It says it completed the setup!!!

9. wow!! or wtf!!

Here are some of these vows explained

This is also discussed at length here on VS docs on Github

SSIS Tech community page from MS

You will see short and long postings on many aspects of SSIS

Show images for: ssis

Installing visual studio

SSIS is primarily documented here: tutorials, lesson plans etc from MS

SSIS VS 2019 Marketplace extension home page is here

Tutorials are documented here

In that tutorial creating a new ssis project is here

However I got this picture after "downloading" the extension. So you may see "download" against it as opposed to a green check box. It is not clear why it says download, even though the vs 2019 installed it using the data storage work loads

Directory structure of an SSIS project

1. For a project template, search for "integration" as the key word. Don't use "ssis" for that is too narrow

2. There is an SSIS version that is for azure, so make sure you pick the non azure one if your goal is a plain ssis project template

3. vs will create a sub directory with the project name, so locate the parent directory or repo where you want to put this stuff.

4. You can see the details of the created artifacts on the right side in the solution explorer, which you can see above as well.

5. You can rename the created "package.dtsx" to a relevant name as you can add more packages to this project

SSIS convert datetime from ISO format

SSIS convert datetime from ISO format

Search for: SSIS convert datetime from ISO format

Here is how you add an OLEDB connection manager on localhost to SSIS

How can I add additional columns to an ssis input source?

Search for: How can I add additional columns to an ssis input source?

Adding derived columns

How can I map the same input field to multiple output fields in SSIS

Search for: How can I map the same input field to multiple output fields in SSIS

SSIS mapping expression language examples

Search for: SSIS mapping expression language examples

How can I create a derived column from 2 columns in SSIS

Search for: How can I create a derived column from 2 columns in SSIS

Script component is documented here

Derived column transformation is here

warning ssis: One or more columns do not have an alias assigned

Search for: warning ssis: One or more columns do not have an alias assigned

what are typecast functions in SSIS?

Search for: what are typecast functions in SSIS?

Data Conversion conversation on SOF

1. From a csv file insert into an equivalent table that has an identity column

2. Flat file connection manager. Defines columns in the file and their data types. ISO date time stamp points to DT_Date type

3. OLE DB connection manager. for the database.

4. Flat file source reading from the file.

5. Columns are not sufficient to populate the table as the table has additional columns

6. Need a derived column transformation. You cannot map one column to multiple columns. So you have to have each column listed here, even though they are duplicate data but with different column names. ex: last_updated_by, created_by where both point to the logged in user.

7. system variables like the logged in user @[System::CreatorName] can be used as values to the derived columns

8. Finally the OLEDB destination with a table or view. Here the mappings have to be one to one. (see above). There is a strange behavior with keep identity. when you want the identity to be automatically updated, turn off this flag and use "ignore" as the input to this identity column.

Execute SQL Task ssis

Search for: Execute SQL Task ssis

Docs on execute sql task

Script Task is documented here

Script Task in SSIS

Search for: Script Task in SSIS

Script Component in SSIS

Search for: Script Component in SSIS

Script Component is documented here

Using a stored procedure to ingest data into SQL Server using SSIS

Search for: Using a stored procedure to ingest data into SQL Server using SSIS

1. Folks may use OLE DB Command to execute a stored proc for each row

2. This will be expensive as that many stored procs will be called

3. Suggested solutions include using a staging table and run the procs in side the database for each row

4. An alternative to 3 is to use a similar approach using c# script code and Data Table object of .net to call the stored proc with the table as an input

is there a bulk stored proc call in SSIS?

Search for: is there a bulk stored proc call in SSIS?

Here are some notes on bulk inserts

How to store objects with rules in a relational database: an ETL question

Search for: How to store objects with rules in a relational database: an ETL question

1. Entities in a relational database are actually collection of objects that are related

2. when they are individually constructed by transactional systems stored procedures can construct one at a time with all the rules applied and foreign keys, parent keys, and child keys satisfied.

3. what do you do when you try to load a million of them?? Calling stored procs one at a takes too long

4. How do you flatten the objects? where do you apply the business rules? In the database or outside the database?

5. i wonder, if the solution is to apply them in a layer prior to the target database where 1) first the rules are applied on an object by object basis in parallel 2) then splice the objects into multiple streams where each stream is fully constructed accurate table. 3) Then load them using bulk. Just wonder!!!

what is the difference between command and a task in ssis

Search for: what is the difference between command and a task in ssis

How do I pass variables between ssis tasks?

Search for: How do I pass variables between ssis tasks?

Perhaps there is something in this article

1. variables have scope

2. at the package level or task level

3. right click on task to set a break point

4. tasks can define variables which can be set against parameters

user scope in ssis variables

Search for: user scope in ssis variables

Basics of variables in SSIS

what is user:: in ssis variables

Search for: what is user:: in ssis variables

Microsoft docs on variables

1. variables can be system defined or user defined

2. users can define variables at a package, task, container etc.

1. name

2. namespace(This is where I have doubts on)

3. whether it can raise on event on value change

4. can be an expression

Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. You can create additional namespaces for user-defined variables and change the name of the User namespace, but you cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.

Only 2 namespaces: user and system

Oh, sorry, looks like you can define additional namespaces if you like

ftp task is documented here at MS

How do I list the files that an ftp task just received in ssis?

Search for: How do I list the files that an ftp task just received in ssis?

Basics of foreach container

A set of articles on SSIS

Advanced expressions in ssis

FileSystem task docs